Select JSON in postgres 9.5

Question

There is a column "fields" in my table. It contains something like this:

{"creative_url": {"required": true, "data_type": "string"}}

or

{"creative_name_field": {"required": false, "data_type": "integer"}}

So, different keys in main dict.

I want to select all rows with:

"required": true

Trying something like :

select *
from my_table
where fields @> '{"required":true}'

But it shows me nothing (0 rows found).

Postgres documentation doesn't give me any other method to do this. What's the problem?

Marth · Accepted Answer · 2016-12-30 14:38:52Z

The @> operator checks only at top level.
You can use the jsonb_object_keys function to get the top level keys of your jsonb objects (use json_object_keys if you're using the json type instead).

WITH j(json) AS (
  VALUES 
    ('{"creative_url": {"required": true, "data_type": "string"}}'::jsonb),
    ('{"creative_name_field": {"required": false, "data_type": "integer"}}')
)
SELECT 
  json,
  json->k @> '{"required": true}' AS is_required
FROM j, jsonb_object_keys(json) s(k)
;
┌──────────────────────────────────────────────────────────────────────┬─────────────┐
│                                 json                                 │ is_required │
├──────────────────────────────────────────────────────────────────────┼─────────────┤
│ {"creative_url": {"required": true, "data_type": "string"}}          │ t           │
│ {"creative_name_field": {"required": false, "data_type": "integer"}} │ f           │
└──────────────────────────────────────────────────────────────────────┴─────────────┘
(2 rows)

MaNKuR · Accepted Answer · 2017-01-10 09:18:20Z

0

Though answer is accepted already and OP is satisfied with answer but I am still adding solution to those who may need helps. It seems you have two level search in the db with json object column. The simplest way to get the record is

select * from my_table where fields->'creative_name_field'->>'required'='true' OR fields->'creative_url'->>'required'='true';

There is other way of doing this but I haven't tested it

select * from my_table where fields->{'creative_url', 'creative_name_field'}->>'required'='true';

There is one catch here is to use the parent key before the is_required sub-key check. Hope This will help.

edited Jan 10, 2017 at 9:18

answered Jan 10, 2017 at 7:38

MaNKuR

2,7141 gold badge22 silver badges31 bronze badges

2 Comments

Snobby Over a year ago

This example will not work if I want to select all fields ('creative_name_field' and 'creative_url') with required='true'

MaNKuR Over a year ago

Oh I missed the point So, different keys in main dict.. But now I have corrected the solution.

Collectives™ on Stack Overflow

Select JSON in postgres 9.5

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related