CODEWITHSUNDEEP
postgresql
user3544162
user3544162

Reputation: 45

How to search column based on json column in PostgreSQL

I have JSON column like this

data (column name)

row 1 [58,96,36,196,25,23] 
row 2 [26,654,269,22,96]

how to write a query

$user = 96;

how to search $user using PostgreSQL database, how do we write a query using PostgreSQL database, based on $user search entire columns of the table

Upvotes: 1

Views: 98

Answers (2)

user330315
user330315

Reputation:

Assuming you are using jsonb (which is highly recommended) you can use the contains operator @>:

select *
from the_table
where data @> '96'::jsonb

If you are using Postgres 12, you can also use the new JSONB path expression:

select *
from the_table
where data @? '$[*] ? (@ == 96)'

Upvotes: 0

User9123
User9123

Reputation: 1733

Use JSON functions, something like:

with table_with_json as (
    select '[58,96,36,196,25,23]'::json json_column union all
    select '[26,654,269,22,96]'::json
)
select 
    twj.json_column 
from 
    table_with_json twj 
where 
    exists (select t.value from json_array_elements(twj.json_column) t where t.value::text = $user::text);

Upvotes: 0

Related Questions