PostgreSQL jsonb string format

Question

I'm using PostgreSQL jsonb and have the following in my database record:

{"tags": "[\"apple\",\" orange\",\" pineapple\",\" fruits\"]",
"filename": "testname.jpg", "title_en": "d1", "title_ja": "1",
"description_en": "d1", "description_ja": "1"}

and both SELECT statements below retrived no results:

SELECT "photo"."id", "photo"."datadoc", "photo"."created_timestamp","photo"."modified_timestamp" 
FROM "photo" 
WHERE datadoc @> '{"tags":> ["apple"]}';

SELECT "photo"."id", "photo"."datadoc", "photo"."created_timestamp", "photo"."modified_timestamp" 
FROM "photo" 
WHERE datadoc -> 'tags' ? 'apple';

I wonder it is because of the extra backslash added to the json array string, or the SELECT statement is incorrect.

I'm running "PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit" on Windows 10.

PostgreSQL doc is here.

IMSoP · Accepted Answer · 2018-03-28 09:04:13Z

2

As far as any JSON parser is concerned, the value of your tags key is a string, not an array.

"tags": "[\"apple\",\" orange\",\" pineapple\",\" fruits\"]"

The string itself happens to be another JSON document, like the common case in XML where the contents of a string happen to be an XML or HTML document.

["apple"," orange"," pineapple"," fruits"]

What you need to do is extract that string, then parse it as a new JSON object, and then query that new object.

I can't test it right now, but I think that would look something like this:

(datadoc ->> 'tags') ::jsonb ? 'apple'

That is, "extract the tags value as text, cast that text value as jsonb, then query that new jsonb value.

answered Mar 28, 2018 at 9:04

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dilli Babu Kadati · Accepted Answer · 2020-05-13 04:11:48Z

Hey there i know this is very late answer, but here is the good approach, with data i have.

initital data in db:

 "{\"data\":{\"title\":\"test\",\"message\":\"string\",\"image\":\"string\"},\"registration_ids\":[\"s
tring\"],\"isAllUsersNotification\":false}"

to convert it to json

select (notificationData #>> '{}')::jsonb from sent_notification

result:

 {"data": {"image": "string", "title": "string", "message": "string"}, "registration_ids": ["string"], "isAllUsersNotification": false}

getting a data object from json

select (notificationData #>> '{}' )::jsonb -> 'data' from sent_notification;

result:

 {"image": "string", "title": "string", "message": "string"}

getting a field from above result:

select (notificationData #>> '{}' )::jsonb -> 'data' ->>'title' from sent_notification;

result:

 string

performing where operations,

Q: get records where title ='string'

ans:

select * from sent_notification where (notificationData #>> '{}' )::jsonb -> 'data' ->>'title' ='string'

Collectives™ on Stack Overflow

PostgreSQL jsonb string format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related