Deep search within jsonb field PostgreSQL

Question

A sample of my data looks something like this:

{"city": "NY", 
"skills": [
{"soft_skills": "Analysis"},
{"soft_skills": "Procrastination"},
{"soft_skills": "Presentation"}
],
"areas_of_training": [
{"areas of training": "Visio"},
{"areas of training": "Office"}, 
{"areas of training": "Risk Assesment"}
]}

I would like to run a query to find users with soft_skills Analysis and maybe run another one to find users whose area of training is Visio and Risk Assesment

My column type is jsonb. How can I implement a search query on these deeply nested objects? A query on level one for city works using SELECT * FROM mydata WHERE content::json->>'city'='NY';

How can I also run a match using the LIKE keyword or string matching for deeply nested values?

Abelisto · Accepted Answer · 2017-07-10 09:26:45Z

23

1)

SELECT * FROM mydata
WHERE content->'skills' @> '[{"soft_skills": "Analysis"}]';

2)

SELECT * FROM mydata
WHERE content->'areas_of_training' @> '[{"areas of training": "Visio"},{"areas of training": "Risk Assesment"}]';

About JSON(B) operators

PS: And be ready for extremely slow queries. I highly recommend to think about data normalization.

Update for LIKE

For your example data it could be:

SELECT * FROM mydata
WHERE EXISTS (
  SELECT *
  FROM jsonb_array_elements(content->'areas_of_training') as a
  WHERE a->>'areas of training' ilike '%vi%');

But query highly depending on the actual JSON structure.

edited Jul 10, 2017 at 9:26

answered Jul 9, 2017 at 20:11

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

5 Comments

Churchill Over a year ago

I have edited the question to include possibility of using the LIKE keyword or string matching.

Churchill Over a year ago

I would have liked to have a better normalized structure but this data is coming from some dynamic form generator hence the need to find a way round its result structure.

klin Over a year ago

Consider an automated import of the data to a normalized table(s).

Abelisto Over a year ago

@Churchill Ok, got it.

Damien Sawyer Over a year ago

With regards to the comment on slow performance, could that be addressed with a GIN index? stackoverflow.com/a/18405706/494635

klin · Accepted Answer · 2017-07-10 06:40:29Z

7

Use json_array_elements() to get values of nested elements, examples:

select d.*
from mydata d,
json_array_elements(content->'skills')
where value->>'soft_skills' ilike '%analysis%';

select d.*
from mydata d,
json_array_elements(content->'areas_of_training')
where value->>'areas of training' ~* 'visio|office';

It is possible that the query yields duplicate rows, so it is reasonable to use select distinct on (id), where id is a primary key.

Note that the function json_array_elements() is costly and you cannot use indexes in contrary to Abelisto's solution. However you have to use it if you want to have an access to values of nested json elements.

answered Jul 10, 2017 at 6:40

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

Churchill Over a year ago

This also works though I had to change my column data type to json and not jsonb for the json_array_elements to work without casting using ::

klin Over a year ago

If the column is jsonb just use jsonb_array_elements().

Collectives™ on Stack Overflow

Deep search within jsonb field PostgreSQL

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related