I have a table (named members) that has a JSON column (named collection). The JSON column stores an array of integers.
Example:
collection
----------
[1]
[5,4,0]
[9,3,1]
How do I query to see if an integer is in that array?
Example: Select all the members that have a 3 in their collection (can be [1] or [1,2,3,4,etc]).
Is it possible?
You can use the json / jsonb containment operator @>.
Since your JSON array seems to contain only numbers, the simple syntax works:
SELECT *
FROM tbl
WHERE collection @> '3'; -- single quotes required!
If you omit the quotes, Postgres resolves the numeric literal to integer and doesn't find an operator for json @> integer.
With quotes, the string literal is resolved to json / jsonb respectively (containing the JSON primitive type number) derived from the context, and it works.
This works just as well, and may be preferable:
SELECT *
FROM tbl
WHERE collection @> '[3]';
Now, the right operand resolves to json / jsonb containing the JSON primitive type array - containing a single number.
This also works for objects as array element types, which would not work without the array wrapper.
To make this fast, use the type jsonb rather than json because that enjoys index support. Either with the default operator class jsonb_ops (default does not have to be spelled out):
CREATE INDEX tbl_jsb_idx ON tbl USING GIN (collection);
Or with the more specialized (but also more efficient) operator class jsonb_path_ops (non-default has to be spelled out):
CREATE INDEX tbl_jsb_idx ON tbl USING GIN (collection jsonb_path_ops);
See:
Use the json operator @>, which means “contains”:
select
collection @> 3 as has_3
If your column is text that is json, use collection::json @> 3
in my case, I have json array column in second table ["Amarawati", "Amravati","AMRAVATI"] and Im trying to join with first table varchar column Amarawati
Example Query:
select a.* from varchar_district a
left join json_array_table_district ddc on
ddc.fp_district::jsonb @> (concat('["',ba.district,'"]',''))::jsonb
