Write query to search inside json record, inside another json in postgreSQL

Question

I have a table which records games of every day. The TEAMS table contains a column that contains json rows, each json string also contains another, something like below:

---------------------------------------------------------------------
| id | doc                                                          |
---------------------------------------------------------------------
| 1  | {'team1':{'num':3, 'players':{'bob', 'eli', 'jack'}, 'color':'red'},
        'team2':{'num':3, 'players':{'a', 'eli', 'x'}, 'color':'blue'}}

This says that team1 and team2 had a game on a day. Can I write a query which retrieves all of records that has 'eli' as player? Thanks for your help

I know the below query is wrong, but is there any correct way? `select * from teams, json_object_keys(doc) sub(a_team) , json_object_keys(json_extract_path(doc, a_team, 'players')) sub(name) where name='eli'` — Ehsan Toghian, Sep 09 '15 at 10:58

score 1 · Accepted Answer · edited May 23 '17 at 11:52

Caveat - I've only just begun working with JSON on Postgresql, so the following works, but might be sub-optimal...

Is that your actual JSON? Because Postgresql 9.3 coughed when I tried to import it; two problems, single quote and not double quotes, and your players were surrounded in curly braces and not square braces.

Anyway. If I got this right, I used this JSON:

create table json_table(data json);
insert into json_table(data)
values('{"team1":{"num":3, "players":["bob", "eli", "jack"], "color":"red"},
        "team2":{"num":3, "players":["a", "eli", "x"], "color":"blue"}}')

The following query will work. Apparently, 9.4 has some additional functions that might make your life easier.

SELECT DISTINCT teamname FROM 

    (SELECT 
    json_data.key AS teamname,
    json_array_elements(json_data.value->'players')::text as players
    FROM 
    json_table,json_each(data) AS json_data)
a

WHERE players = '"eli"'

Sam Hughes · Answer 2 · 2021-08-07T03:01:11.037

Stale question, sure, but it's got a much better answer nowadays.

There's a simple version, where you don't care about the top-level key, only the top-level value:

select json_path_query(
    doc, 
    '$.* ? (@.players[*] == $player)', 
    '{"player": "eli"}'
) from TEAMS

If you want the top-level key, I don't have a good suggestion that doesn't involve conversion and/or expansion.

select json_path_query(
    (team).value, 
    '$ ? (@.players[*] == $player)', 
    '{"player": "eli"}'
) from (
    select json_each(doc) as team from TEAMS
) schedule

As this is an XY problem, most definitely, you might be interested in knowing whether eli's brother, "ERROR:ROOT", played that day too. You can always pass an array of values in for that third argument to json_path_query(), like so:

select json_path_query(
    doc,
    '$.* ? (@.players[*] == $player[*])',
    '{"player": ["eli","ERROR:ROOT"]}'
) from TEAMS

Oh! That raises a really good possibility. Say you have a team, and you want to know what other days any of those players are up. You might do something like so:

select json_path_query(
    doc,
    '$.* ? (@.players[*] = $players[*])',
    '{"num":3, "players": ["a", "eli", "x"], "color":"blue"'
) from TEAMS

If you want to know more, the PostgreSQL docs are expansive but fantastically detailed. There are tons of great examples in there, both at the page on JSON datatype and JSONpath, but also the page on JSON functions.

The JSON datatype: https://www.postgresql.org/docs/13/datatype-json.html JSON functions: https://www.postgresql.org/docs/13/functions-json.html

Write query to search inside json record, inside another json in postgreSQL

2 Answers2