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.

Answer 1

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