Postgresql json在9.3中查询嵌套的有效载荷

概述如果我有一个名为“races”的简单表,使用 postgresql 9.3(和新的json awesomne​​ss),它有两列描述,如： race-id integer,race-data json 而且,json是每个种族的有效载荷 { "race-time": some-date, "runners": [ { "name": "fred","age": 30, "posit 如果我有一个名为“races”的简单表,使用 postgresql 9.3(和新的Json awesomne​​ss),它有两列描述,如：

race-ID integer,race-data Json

而且,Json是每个种族的有效载荷

{      "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    }

如何查询表格：

1)sam已经到来的比赛1

2)sam已经来到第1名,约翰已经来到第2名

3)年龄大于30岁的跑步者人数> 5和奖金> 5000

到目前为止,我的实验(特别是查询嵌套阵列有效载荷)导致进一步使数据正常化,即创建一个名为跑步者的表来进行这种查询.理想情况下,我想使用这个新的fangled Json查询awesomeness,但我似乎无法做出头或尾部相应的3个简单的查询.

您可以将Json放在一个记录中,然后根据需要进行查询(参见 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 cte2where name1 = 'sam' and name2 = 'john'

sql fiddle demo

由于您的JsON结构,这有点复杂.我认为,如果你改变你的结构,你的查询可能会更简单：

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

您可以使用 – >>和 – >运算符或Json_extract_path_text函数来获取所需的数据,然后在where子句中使用它：

select *from races as rwhere    r.race_data->'runners'->'1'->>'name' = 'sam';select *from races as rwhere    Json_extract_path_text(r.race_data,'runners','1','name') = 'sam' and    Json_extract_path_text(r.race_data,'2','name') = 'john';select *from races as rwhere    (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在9.3中查询嵌套的有效载荷全部内容，希望文章能够帮你解决Postgresql json在9.3中查询嵌套的有效载荷所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。