CODEWITHSUNDEEP
postgresql
Alan
Alan

Reputation: 241

Querying jsonb that contains arrays and booleans

I have a table with a jsonb field. The content looks like this:

select myfield from mytable

{"1": [1, 2, 3], "2": [1, 2], "3": [1, 2], "4": true}
{}
{"1": [6, 11]}
{"3": [2], "4": true}

So far I've managed to run queries like:

-- Select all rows that, for key "1", are null
select addl from live_feeds where (addl->>'1') is null

-- Select all rows that, for key "4", are true
select addl from live_feeds where (addl->>'4')::boolean is true;

But after trying a few different approaches, I couldn't get this one to work:

Select all rows that, for key "1", include value 1

What kind of casting/operator should I use for this?

Upvotes: 0

Views: 27

Answers (1)

hgb123
hgb123

Reputation: 14891

You could do this (function json doc)

@>: Does the left JSON value contain within it the right value?

select addl from live_feeds where (addl->>'1')::jsonb @> '[1]'

Upvotes: 1

Related Questions