Postgres: Range Query on nested jsonb column

Question

I am using postgres 9.5.4.

From below sample json data stored in jsonb column, I would like to search records matching o['mid'] > 7000

This is sample of one user's record. There will be millions of such users.

{
  "uid": 14105529,
  "o": [
    {
      "mid": 6551,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    },
    {
      "mid": 7552,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    },
    {
      "mid": 7553,
      "ac": 1913,
      "ip": "144.36.233.44",
      "adw": 5,
      "at": 133000,
      "ad": 151015,
      "aid": 0
    }
  ]
}

klin klin · Accepted Answer · 2016-10-14T11:34:51

with a_table(jdata) as ( 
values (
    '{
        "uid":14105529,
        "o":[
            {"mid":6551,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0}, 
            {"mid":7552,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0},
            {"mid":7553,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0} 
        ] }'::jsonb
    )
)

select jdata->'uid' as uid, value
from a_table, jsonb_array_elements(jdata->'o')
where (value->>'mid')::int > 7000;

   uid    |                                              value                                               
----------+--------------------------------------------------------------------------------------------------
 14105529 | {"ac": 1913, "ad": 151015, "at": 133000, "ip": "144.36.233.44", "adw": 5, "aid": 0, "mid": 7552}
 14105529 | {"ac": 1913, "ad": 151015, "at": 133000, "ip": "144.36.233.44", "adw": 5, "aid": 0, "mid": 7553}
(2 rows)

The query will be really expensive for a large dataset because of necessity of unnesting the json array with jsonb_array_elements(). There is no index you could use to speed it up.

Postgres: Range Query on nested jsonb column

1 Answers