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?

Answer 1

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.

Answer 2

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.