Postgres query on nested array failing to return results

Question

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

klin · Accepted Answer · 2022-02-27 13:16:55Z

1

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%';

answered Feb 27, 2022 at 13:16

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

meds Over a year ago

that was it! I and really appreciate the simpler example - much nicer syntax for json selection

Collectives™ on Stack Overflow

Postgres query on nested array failing to return results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related