CODEWITHSUNDEEP
postgresqljsonb
Churchill
Churchill

Reputation: 1607

Deep search within jsonb field PostgreSQL

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?

Upvotes: 14

Views: 37403

Answers (2)

Abelisto
Abelisto

Reputation: 15614

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.

Upvotes: 22

klin
klin

Reputation: 121604

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.

Upvotes: 7

Related Questions