'PostgreSQL find by value in array in jsonb data

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]|
postgresqljsonb


Solution 1:[1]

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

Online example

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name
Solution 2 Ramin Faracov

Related Questions