postgresql - 范围日期的JSON查询

Question

I have data json like this

{"2018-05": /*year-month*/ {"20": /*date*/{"price": 50, "stock": 12}, "21":/*date*/{"price": 60, "stock": 5}, "25": /*date*/{"price": 55, "stock": 0}  }}

how I can make query for range between 2 date and stock more than 0?

sorry for My bad English

Answer 1

I assume you are trying to do smth like:

t=# with c(j) as (values('{"2018-05":{"20":{"price":50,"stock":12},"21":{"price":60,"stock":5},"25":{"price":55,"stock":0}}}'::json))
, s as (select j,json_object_keys(j->'2018-05') k from c)
select j->'2018-05'->k from s
where k::int between 20 and 22
and (j->'2018-05'->k->>'stock')::int > 0;
        ?column?
-------------------------
 {"price":50,"stock":12}
 {"price":60,"stock":5}
(2 rows)