How can I get records from table where array in column value contains any value to find.
Well, the column can contain any data type of array, objects, strings, etc and null value. And arrays in column can contain any serializable data type
id|value |
--+------------+
1|null |
2|[0.05, 0.11]|
You can use a JSON path expression:
select *
from the_table
where value @@ '$[*] == 0.11'
If the column doesn't contain an array, you can use
select *
from the_table
where value @@ '$.* == 0.11'
This assumes value is defined as jsonb (which it should be). If it's not, you have to cast it value::jsonb
Some samples:
-- sample 1
with sample_data as (
select 1 as "id", null::jsonb as "value"
union all
select 2 as "id", '[0.05, 0.11]'::jsonb as "value"
)
select a2.pval::float4 from sample_data as a1
cross join jsonb_array_elements(a1."value") as a2(pval)
--Return:
0.05
0.11
-- sample 2
with sample_data as (
select 1 as "id", null::jsonb as "value"
union all
select 2 as "id", '[0.05, 0.11]'::jsonb as "value"
)
select a2.pval::float4 from sample_data as a1
cross join jsonb_array_elements(a1."value") as a2(pval)
where a2.pval::float4 > 0.1
--Return:
0.11
