CODEWITHSUNDEEP
postgresqljsonb
Kamo Petrosyan
Kamo Petrosyan

Reputation: 234

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

Upvotes: 0

Views: 451

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 1

Ramin Faracov
Ramin Faracov

Reputation: 3303

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

Upvotes: 0

Related Questions