Home > Enterprise >  PostgreSQL find by value in array in jsonb data
PostgreSQL find by value in array in jsonb data

Time:04-06

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

CodePudding user response:

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

CodePudding user response:

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

  • Related