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 · Accepted Answer · 2016-10-14 11:34:51Z

2

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.

answered Oct 14, 2016 at 11:34

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Carbonrock Over a year ago

Thanks a lot Klin. This works for me. As you mentioned, need to check performance on large dataset.

Carbonrock Over a year ago

@ klin, facing issue while executing this query on citus cluster. Need help. postgres=# select count(1) from jsontest, jsonb_array_elements(data->'o') where (value->>'mid')::int>7000; ERROR: cannot perform distributed planning on this query DETAIL: Complex table expressions are currently unsupported

klin Over a year ago

Unfortunately, this is not a standard Postgres error message. It seems citus has additional limitations, see this citus issue.

Collectives™ on Stack Overflow

Postgres: Range Query on nested jsonb column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related