I have the following query:
SELECT * FROM post p
, jsonb_array_elements(p.meta #> '{tags, tag}') d
WHERE d->>'tag' LIKE '%mytag%';
Which isn't returning any results, I have an object in the post table that looks like this:
I'm not sure why this is failing, my understanding was jsonb_array_elements flattens out arrays and by selecting {tags, tag} it would get the tag string
CodePudding user response:
The path to the nested array is meta #> '{tags}'
, not meta #> '{tags, tag}'
, so this should work
select *
from
post p,
jsonb_array_elements(p.meta #> '{tags}') d
where d->>'tag' like '%mytag%';
-- or simpler
select *
from
post p,
jsonb_array_elements(p.meta -> 'tags') d
where d->>'tag' like '%mytag%';