postgresql query json for value (instead of key)?

Question

Imagine this simple postgresql table ('json_table'):

id | data(json)
1  | {"a": 1}
2  | {"a": 1, "b": 2}
3  | {"a": 2}

I know it's possible to query for all records having a key 'a': SELECT * FROM json_table WHERE data ? 'a';

But I would like to query all records containing a json value '2'. So the result should be this:

2  | {"a": 1, "b": 2}
3  | {"a": 2}

Is this possible with a 'simple' query like the one mentioned above?

klin · Accepted Answer · 2015-06-17 17:04:06Z

1

I do not think there is an operator which you can use in such a simple query. You can achieve this with the query below, though I do not know whether it is the simplest way:

select distinct on (id) id, data from (
    select id, data, (json_each(data)).value::text
    from json_table
    ) alias
where value = '2'

answered Jun 17, 2015 at 17:04

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

postgresql query json for value (instead of key)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related