checking for an existence of a key in postgresql jsonb column

Question

I have a jsonb column in postgres db. I need to query for an existence of specific key "ssn" in the json stored in the column. This key is not a top level key, but is nested. I wrote a query similar to below but it doesn't seem to retrieve the appropriate columns -

select * from application where content ? 'ssn';

When I run the above query with top level keys , it works.

{  
   "name":"bob",
   "business":{  
      "ssn":"XXXXXXXX",
    }
}

Can you provide a sample of json that is not retrieved by this query? — Ivan Mogila
– Ivan Mogila, Commented Feb 15, 2018 at 17:11

Ivan Mogila · Accepted Answer · 2018-02-15 18:15:41Z

2

If you know all possible locations of ssn key you can use query:

select *
from application
where content #> '{business,ssn}' is not null
  or content #> '{whatever,deep,ssn}' is not null;

answered Feb 15, 2018 at 18:15

Ivan Mogila

4672 silver badges9 bronze badges

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

1 Comment

Punter Vicky Over a year ago

Thanks @Ivan. I just added additional question under Haleemur's answer.

Haleemur Ali · Accepted Answer · 2018-02-15 18:16:07Z

2

use a extract-element-at-path operator #>> and check whether the value at the given path is null.

example:

WITH test(col) AS (
SELECT UNNEST(
  ARRAY['{"name": "bob", "business": {"ssn": "abcd"}}'::jsonb, 
        '{"name": "jen", "business": {"ssn": "1234"}}',   
        '{"name": "kay", "nonprofit": {"no-ssn": "no-ssn"}}'
        '{"name": "todd", "business": {"no-ssn": "no-ssn"}}']
))
SELECT * FROM test
WHERE col#>>'{business, ssn}' IS NOT NULL

answered Feb 15, 2018 at 18:16

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

2 Comments

Punter Vicky Over a year ago

Thanks @Haleemjur Ali. This works fine for the example , but doesn't work in a scenario say where business element is an array under another root element. How can I handle arrays as part of this query?

Haleemur Ali Over a year ago

add a better example, if you want help making a solution that works.

Vao Tsun · Accepted Answer · 2018-02-16 09:04:44Z

1

you

either have to write the recursive query to parse the structure of json and check for the key in every branch,
or simply match pattern against text representation of jsonb

smth like:

select * from application 
where content::text like '%"ssn":%';

answered Feb 16, 2018 at 9:04

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

checking for an existence of a key in postgresql jsonb column

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related