#15356: Table Data Editor: Updating JSON value has missing '

tariqrahiman reported 2017-06-28T17:54:22Z · last modified 2017-07-05T20:20:41Z

Table Data Editor: Updating JSON value has missing '

Dev	tomconrad
QA	JennyNishimura

Priority	Minor
Complexity	Unknown

Component	DB - PostgreSQL
Version	19.0

Version: 18.0.17-1

Build #: 54886

Build Date: 2017-Jun-26 02:44:01 PM

Table Data Editor: Updating JSON value has missing '

1. Navigate to to PostgreSQL v9.6 server ar 1.193

2. Take Edit Table Data for Tom database for table named tde in the public schema

3. Update pageName and update to abcd instead of abc

4. Go to Primary Key tab and select a primary key - either c1 or c2

5. Go to Preview SQL

6. You can see that in the WHERE clause after = there is a missing '

In Version: 18.0.17, the ' was appearing correctly. See attached screenshot

SQL generated in 18.0.17-1

UPDATE "public"."tde"

SET "c1"='{ "pageInfo" : { "pageName" : "abcd" ,"pagePic" : "http://example.com/content.jpg" } ,"posts" : [{"post_id" : 12345, "actor_id" : "544","page_id" : 977}]}'

WHERE "c1"={ "pageInfo" : { "pageName" : "abc" ,"pagePic" : "http://example.com/content.jpg" } ,"posts" : [{"post_id" : 12345, "actor_id" : "544","page_id" : 977}]} AND "c2"={"name": "Book the First", "author": {"last_name": "White", "first_name": "Bob"}}

SQL generated in 18.0.17

UPDATE "public"."tde"

SET "c1"='{ "pageInfo" : { "pageName" : "abcd" ,"pagePic" : "http://example.com/content.jpg" } ,"posts" : [{"post_id" : 12345, "actor_id" : "544","page_id" : 977}]}'

WHERE "c1"='{ "pageInfo" : { "pageName" : "abc" ,"pagePic" : "http://example.com/content.jpg" } ,"posts" : [{"post_id" : 12345, "actor_id" : "544","page_id" : 977}]}'

4 attachments

Screen Shot 2017-06-28 at 10.36.25 am.png

2017-06-28T17:54:22Z

101 KB
Error_Message.png

2017-06-29T15:06:24Z

153 KB
Update_TBD_v19.png

2017-07-04T13:33:22Z

82 KB
Delete_TBD_v19.png

2017-07-04T13:33:22Z

86 KB

All Comments (6) Change History

tomconrad 2017-06-29T21:59:31Z · (edited)

Hi Team,

There is a bigger problem with updating the actual json data in the database that transcends adding a quote to the predicate. The update doesn't seem work in ADS 18.0.17 even with the quote. I don't think we can set the predicate the way tde is doing it. The json might need to be broken down into components using operators in order for PostgreSQL to do the predicate search. Below is a testcase displaying what works and what doesn't. ~~I think we should push this fix to a future release since it more complicated then adding quotes.~~

Sachin, What do you think?

Thanks, Tom

CREATE TABLE "public"."t2" (

json_index int NOT NULL,

json_test json NOT NULL,

jsonb_test jsonb NOT NULL

)

INSERT INTO "public"."t2"( json_index, json_test, jsonb_test)

VALUES( 1, '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }', '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }')

INSERT INTO "public"."t2"( json_index, json_test, jsonb_test)

VALUES( 2, '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }', '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }')

INSERT INTO "public"."t2"( json_index, json_test, jsonb_test)

VALUES( 3, '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }', '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }')

INSERT INTO "public"."t2"( json_index, json_test, jsonb_test)

VALUES( 4, '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }', '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }')

INSERT INTO "public"."t2"( json_index, json_test, jsonb_test)

VALUES( 5, '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }', '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }')

select * from "public"."t2"

// This will not work.

UPDATE "public"."t2"

SET "json_test"='{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }'

WHERE "json_test"='{ "name": "Book the First", "authorz": { "first_name": "Bob", "last_name": "White" } }'

// This will work.

UPDATE "public"."t2"

SET "json_test"='{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }'

WHERE "json_test" ->> 'name' = 'Book the First'

// This will work.

UPDATE "public"."t2"

SET "json_test"='{ "name": "Book the First", "authorz": { "first_name": "Bob", "last_name": "White" } }'

WHERE "json_index" = 1