Postgresql json querying in 9.3+ on nested payloads

Question

Using postgresql 9.3 (and the new json awesomness) if I have a simple table named 'races' with a two column description such as:

race-id integer,
race-data json

And the json is a payload for each race is something like

{      "race-time": some-date,
  "runners": [ { "name": "fred","age": 30, "position": 1 },
               { "name": "john","age": 29, "position": 3 },
               { "name": "sam","age": 31, "position": 2 } ],
  "prize-money": 200    }

How can I query the table for:

1) Races where sam has come 1st

2) Races where sam has come 1st and john has come 2nd

3) Where the number of runners with age greater than 30 is > 5 and prize-money > 5000

My experimentation (particularly in querying a nested array payload) so far has lead to further normalizing the data, i.e. creating a table called runners just to make such queries. Ideally I'd like to use this new fangled json query awesomeness but I can't seem to make heads or tails of it in respective to the 3 simple queries.

Roman Pekar Roman Pekar · Accepted Answer · 2013-12-04T13:22:25

You can unwind json into one record and then do your queries as you want (see json functions):

with cte as (
    select
       race_id,
       json_array_elements(r.race_data->'runners') as d,
       (r.race_data->>'prize-money')::int as price_money
    from races as r
), cte2 as (
    select
        race_id, price_money,
        max(case when (d->>'position')::int = 1 then d->>'name' end) as name1,
        max(case when (d->>'position')::int = 2 then d->>'name' end) as name2,
        max(case when (d->>'position')::int = 3 then d->>'name' end) as name3
    from cte
    group by race_id, price_money
)
select *
from cte2
where name1 = 'sam' and name2 = 'john'

sql fiddle demo

It's a bit complicated because of your JSON structure. I think that if you change your structure a bit, your queries could be much simplier:

{
  "race-time": some-date,
  "runners":
   {
      "1": {"name": "fred","age": 30},
      "2": {"name": "sam","age": 31},
      "3": {"name": "john","age": 29}
   },
  "prize-money": 200
}

you can use ->> and -> operators or json_extract_path_text function to get data you need and then use it in the where clause:

select *
from races as r
where
    r.race_data->'runners'->'1'->>'name' = 'sam';

select *
from races as r
where
    json_extract_path_text(r.race_data, 'runners','1','name') = 'sam' and
    json_extract_path_text(r.race_data, 'runners','2','name') = 'john';

select *
from races as r
where
    (r.race_data->>'prize-money')::int > 100 and
    (
        select count(*)
        from json_each(r.race_data->'runners')
        where (value->>'age')::int >= 30
    ) >= 2

sql fiddle demo

Postgresql json querying in 9.3+ on nested payloads

1 Answers