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 http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype — Mahesh, Feb 16 '15 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, Feb 16 '15 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, Feb 16 '15 at 18:14
@ErwinBrandstetter - added more information about table schema. Is it OK? — NilColor, Feb 16 '15 at 18:43
Any particular reason why you don't use a JSON array like `'[{"name":"Azamund","weight":"3"}, {"name":"Iskapola","weight":"1"}]'` Note the square brackets. Or even a normalized db schema without JSON? — Erwin Brandstetter, Feb 16 '15 at 19:25
@ErwinBrandstetter sure I can change schema and migrate data... If there is no simple/pretty way to retrieve data. And do not want to normalize this data... `blueprint` can contains other keys (other than name/weight) and that keys is unknown. — NilColor, Feb 16 '15 at 19: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, Feb 16 '15 at 20:16

Erwin Brandstetter · Accepted Answer · 2023-08-01T20:12:48.833

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

Nice improvements! Thank you. – NilColor Feb 16 '15 at 19:54 — NilColor, Feb 16 '15 at 19:54

score 1 · Answer 2 · answered Feb 16 '15 at 15:41

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 '15 at 15:41

NilColor

3,462
3
30
44

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

2 Answers2

Alternative with JSON array

With JSON/PATH expression in Postgres 12+

Linked