Querying Entire JSON field data in PostgreSQL

Question

I was trying to check on a way to query entire json field data in PostgreSQL. But, all I see is querying for a specific field like below.

# SAMPLE DATA
# data = {"test": 1, "test_another": 2}

select * from sample_table where data->>'test' = ('1')

The above condition yields the correct result. But, I have tried querying on the entire block in multiple ways, but, it dosent seem to work and returns below error.

select * from sample_table where data->'{"test": 1, "test_another": 2}'

#ERROR:  argument of WHERE must be type boolean, not type json

score 3 · Accepted Answer · 2020-07-17 15:14:04Z

3

Use the contains operator @>

select * 
from sample_table 
where data @> '{"test": 1, "test_another": 2}'

or if you need an exact match:

select * 
from sample_table 
where data = '{"test": 1, "test_another": 2}';

The above requires data to be defined as jsonb (which it should be anyway). If it's only a json you need to cast it:

where data::jsonb = '{"test": 1, "test_another": 2}'

edited Jul 17, 2020 at 15:14

answered Jul 17, 2020 at 15:04

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

ZZzzZZzz Over a year ago

Unfortunately my column is defined as json and not as jsonb

user330315 Over a year ago

Then you have to cast it: where data::jsonb = ...

ZZzzZZzz Over a year ago

Works. Perfect!

Collectives™ on Stack Overflow

Querying Entire JSON field data in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related