How to filter rows on nested values in a json column?

Question

Here is my table (simplified, only significant columns):

CREATE TABLE things (
  id serial primary key
, name varchar
, blueprint json default '{}'
);

And some sample data:

# select * from things;

 id |  name   |                                  blueprint
----+---------+-----------------------------------------------------------------------------
  1 | Thing 1 | {}
  2 | Thing 2 | {"1":{"name":"Iskapola","wight":"2"}}
  3 | Thing 3 | {"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}
  4 | Thing 4 | {"1":{"name":"Ulamir","weight":"1"}, "2":{"name":"Azamund","weight":"1"}}

I'd like to select rows that have 'Azamund' anywhere under the name key. Something like this:

# select * from things where * ->> 'name' = 'Azamund';

 id |      blueprint
----+----------------------------------------------------------------------------
  7 | {"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}
  8 | {"1":{"name":"Ulamir","weight":"1"}, "2":{"name":"Azamund","weight":"1"}}

Data is nested exactly like in the sample (only one level).
Currently we are using PostgreSQL 9.3.5.

Is it possible in PostgreSQL 9.3? Maybe 9.4?

I am not sure but this might help you stackoverflow.com/questions/10560394/… — Mahesh
– Mahesh, Commented Feb 16, 2015 at 14:23
Thank you! I saw that question... But the difference here - I can not write down key path to the name column... It can be under '1' or '9' key. And I still should be able to locate it. Anyway - thank you. I've add more details in question to highlight it... (hope it's clear) — NilColor
– NilColor, Commented Feb 16, 2015 at 14:32
So there is exactly one level of nesting? Also, actual table definitions are always helpful. The data type could be text or json or jsonb? ... NOT NULL? — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 16, 2015 at 18:14
@ErwinBrandstetter - added more information about table schema. Is it OK? — NilColor
– NilColor, Commented Feb 16, 2015 at 18:43
I took the liberty to consolidate your question as I figure I may be referring to it in the future. I hope I caught the gist of it? — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 16, 2015 at 20:16

Erwin Brandstetter · Accepted Answer · 2023-08-01 20:12:48Z

Your query is close. json_each() is the key function. Or jsonb_each() for jsonb. A couple of improvements:

SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM json_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );

db<>fiddle here
_{Old sqlfiddle}

json_each() already returns json values. No need for an additional cast.

Better, yet, use a LATERAL reference in EXISTS. This is much cleaner than unnesting with a set-returning function in the SELECT list. See:

Call a set-returning function with an array argument multiple times

Use ILIKE for the pattern match. Regular expression matches (~, ~*) are more versatile, but also more expensive. So use the basic LIKE / ILKE where you can. See:

Pattern matching with LIKE, SIMILAR TO or regular expressions

Alternative with JSON array

You have already seen my related answer for JSON arrays:

How do I query using fields inside the new PostgreSQL JSON datatype?

While the query for nested JSON objects seems just as simple, there is superior index support for the array:

Index for finding an element in a JSON array

With JSON/PATH expression in Postgres 12+

The syntax is complex, but the query becomes simpler and more efficient. Can also use an index of the form:

CREATE INDEX things_blueprint_gin_idx ON things USING gin (blueprint);

SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name == "Azamund")';

Case insensitive:

SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name like_regex "^azamund$" flag "i")';

For the array variant, we can also use an index with the more efficient operator class jsonb_path_ops:

CREATE INDEX things_arr_foo_path_ops ON things_arr USING gin (blueprint jsonb_path_ops);

SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name == "Azamund")';

Case insensitive:

SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name like_regex "^azamund$" flag "i")';

db<>fiddle here

NilColor · Accepted Answer · 2015-02-16 15:41:52Z

1

Closest query I can execute (that returns data I need) is:

select *
from (select id, (json_each(blueprint)).value::json->>'name' as name
      from stocks) as t
where t.name ~* 'azamund';

Well... Maybe there is something better?

answered Feb 16, 2015 at 15:41

NilColor

3,5423 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to filter rows on nested values in a json column?

2 Answers 2

Alternative with JSON array

With JSON/PATH expression in Postgres 12+

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Alternative with JSON array

With JSON/PATH expression in Postgres 12+

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related