PostgreQL fetch data from Json field which of type multidimensional array

Question

I was finding the query that fetch the data from table of column which of json type.

My table looks like:

`ID | ParentID |        Details`

`-------------------------------------------------------------------------------------------`

`1  |  10      | {"_translated": {"en_US": {"is_draft": "false"}, "default_locale": "en"}}`

`2  |  20      | {"_translated": {"en_US": {"is_draft": "true"}, "default_locale": "en"}}`

`3  |  30      | {"_translated": {"en_CA": {"is_draft": "true"}, "default_locale": "en"}}`

`4  |  40      | {"_translated": {"en_CA": {"is_draft": "false"}, "default_locale": "en"}}`

I want to fetch those row whose is_draft = false.

Please someone can help me this query.

Tim Biegeleisen · Accepted Answer · 2018-10-24 03:57:38Z

2

Given that we don't even know which keys would have child JSON objects with is_draft set to false, one option here would be to just cast the JSON to text and search it using LIKE:

SELECT *
FROM yourTable
WHERE Details::text LIKE '%"is_draft": "false"%';

Demo

answered Oct 24, 2018 at 3:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Mayank Porwal · Accepted Answer · 2018-10-24 03:59:30Z

0

Try:

SELECT 
   id,
   parent_id, 
   details
FROM books
WHERE details->'_translated' -> 'en_US' -> 'is_draft' ='false' 
OR details->'_translated' -> 'en_CA' -> 'is_draft' ='false' ;

edited Oct 24, 2018 at 3:59

answered Oct 24, 2018 at 3:56

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Collectives™ on Stack Overflow

PostgreQL fetch data from Json field which of type multidimensional array

2 Answers 2

Demo

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