Select all PostgreSQL rows where a json column contains a specific key

Question

Given a PostgreSQL 9.3 table with a json column, how do you write a query that selects all rows where the json column includes a specified key? I'm not interested in the values of the json hash, just whether the object includes the given key.

In other words, you can do this in later versions of PostgreSQL by casting the data by data::jsonb ? 'key'. But I'm interested in PostgreSQL 9.3 specifically. — Paul Fletcher-Hill
– Paul Fletcher-Hill, Commented May 2, 2016 at 23:14
Have you tried not casting to jsonb. For example: data ? 'key'? — Graham Kaemmer
– Graham Kaemmer, Commented May 2, 2016 at 23:18
Yeah, the ? operator does not exist for json columns—only hstore and ultimately jsonb. — Paul Fletcher-Hill
– Paul Fletcher-Hill, Commented May 2, 2016 at 23:23
So is upgrading to the current version to make use of the ? operator not an option? — user330315
– user330315, Commented May 3, 2016 at 6:28

Graham Kaemmer · Accepted Answer · 2016-05-04 20:43:42Z

6

If it suits your needs (and you don't store NULL values in your JSON), I think the easiest method is simply:

SELECT * FROM table WHERE (data->'key') IS NOT NULL;

answered May 4, 2016 at 20:43

Graham Kaemmer

3351 silver badge8 bronze badges

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

Comments

cur4so · Accepted Answer · 2016-05-03 03:58:52Z

0

if your table has an extra column with the primary key, this should work

create table t (i integer primary key, j json, t text);
insert into t values 
(1,'{"id1":"a", "id2":"b"}','xxx'),
(2,'{"id1":"a", "id3":"c"}', null), 
(3,'{"id2":"a", "id4":"b"}', null),
(4,'{"id1":"x", "id6":"v"}','yyy');

select t.* from t join (select i as h,json_object_keys(j) as q from t) k on q='id1' and h=i;

http://sqlfiddle.com/#!15/69a72/1

kind of weird, should be a better and more common case solution. I've tried several, no luck

answered May 3, 2016 at 3:58

cur4so

1,8204 gold badges21 silver badges29 bronze badges

Collectives™ on Stack Overflow

Select all PostgreSQL rows where a json column contains a specific key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related