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-10T09:26:45.223

19

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 '17 at 09:26

answered Jul 09 '17 at 20:11

Abelisto

14,826
2
33
41

I have edited the question to include possibility of using the LIKE keyword or string matching. – Churchill Jul 10 '17 at 04:59
1

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. – Churchill Jul 10 '17 at 08:24
2

Consider an automated import of the data to a normalized table(s). – klin Jul 10 '17 at 09:20
@Churchill Ok, got it. – Abelisto Jul 10 '17 at 09:23
1

With regards to the comment on slow performance, could that be addressed with a GIN index? https://stackoverflow.com/a/18405706/494635 – Damien Sawyer Jul 03 '18 at 21:30

score 6 · Answer 2 · answered Jul 10 '17 at 06:40

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.

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 `::` — Churchill, Jul 10 '17 at 08:23

Deep search within jsonb field PostgreSQL

2 Answers2