CODEWITHSUNDEEP
arraysjsonpostgresql
Dubs
Dubs

Reputation: 13

Postgres Where Value in Json Array of Objects

I am working in Postgres V11 and have a table with a field (typeid) of int4 type.

I also have JSONB data I am getting from another table and storing into a local variable.

This data looks like

{"Types":[{"Category": "a", "TypeID": 26}, {"Category": "a", "TypeID": 27}, {"Category": "b", "TypeID": 28}, {"Category": "c", "TypeID": 30}, {"Category": "d", "TypeID": 29}]}

The query I am trying to write is

SELECT * FROM table WHERE typeid is contained in this json data

So, I only want the rows in the table returned if the typeid is listed in the json array.

I have tried different combination of this, but have yet to find something that works.

SELECT * FROM table WHERE typeid @> ANY (jsondata -> 'Types' -> 'TypeID')

The jsondata -> 'Types' seems to return just the array, but I can't seem to figure out how to specify I want to compare against the TypeID value in the object.

Postgres keeps returning

ERROR: op ANY/ALL (array) requires array on right side

So, I tried returning just the array, but it doesn't know what to compare against.

Thanks for the help!

Upvotes: 1

Views: 6817

Answers (2)

jjanes
jjanes

Reputation: 44423

The ANY takes a PostgreSQL array, not an JSON array. Because JSON arrays can have heterogeneous types, they are not trivially convertible to PostgreSQL arrays. You could create a help function to do this conversion.

create function jsonb_to_intarray(jsonb,property text) returns int[] immutable parallel safe language SQL as $$
  select array_agg((x->>$2)::int) from jsonb_array_elements($1) f(x)
$$;

And then:

SELECT * FROM table WHERE typeid =ANY (jsonb_to_intarray(jsondata -> 'Types', 'TypeID'));

Upvotes: 3

user330315
user330315

Reputation:

There is no single operator that you can use. You will need to unnest the array and test each element:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.jsondata -> 'Types') as j(element)
              where j.element ? 'TypeID')

This would list rows where at least one array element contains TypeID.

Upvotes: 1

Related Questions