sql—如何在多个json中找到多个键，并仅返回那些在postgresql9.5中具有某些值的行

4urapxun 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(245)

我有一个包含多个json行的表。在这个json结构中存储了不同的键值，如：

"official_form_attributes":{"81459" : "Y", "81460" : " ", "81293" : "1~Yes", "80985" : " ", "80953" : "1", "80952" : " ", "80951" : "8~Forward", "81291" : "1~Yes", "81295" : "1~Yes", "81294" : "1~Yes", "80986" : "1~PRED", "81292" : "1~Yes", "80954" : "4", "80950" : " "}

"official_form_attributes":{"81321" : "6", "81315" : "15/06/2020", "81364" : "Approved", "81320" : "100000", "81466" : " ", "81314" : "1~Pucca", "80958" : "9~Forward to Tahsildar", "81318" : "20", "81325" : "20", "81465" : "Y", "81322" : "20000", "81324" : "1~Partially Damaged", "81323" : "20000", "81317" : "30", "81326" : "5200", "81319" : "600", "81316" : " "}

"official_form_attributes":{"82817" : " ", "82818" : " ", "82835" : "4", "81486" : "1~Yes", "82855" : "4", "83240" : "29/10950004/2020/07/09/29006271/10950004_9356416_3914_1594303859111.pdf", "81487" : " ", "80963" : "approved", "81488" : "5200", "80962" : "11~Approve by Tahsildar"}.

我得在这张table上找到钥匙。该查询的结果是返回与该键相关的具有某个值和空值的所有行。但我的要求是只返回那些有一些值的行。
案例1我的问题

select application_id, current_process_id, processing_json->'official_form_attributes'->'81488' 
from schm_ka.processing_data_json 
where application_id = 9356416;

结果：

applid      keyvalue
9356416     ""
9356416     ""
9356416     "5200"

但我只需要这个

applid      keyvalue
9356416     "5200"

案例2

select application_id, current_process_id, processing_json->'official_form_attributes'->'81488',processing_json->'official_form_attributes'->'81315' 
from schm_ka.processing_data_json 
where application_id = 9356416;

结果

applid  key1value   key2value
9356416     ""          ""
9356416     ""          "15/06/2020"
9356416     "5200"      ""

但我只需要这个

applid      key1value   key2value
9356416     "5200"      "15/06/2020"

怎么做？

sql postgresql JSON postgresql-9.5

来源：https://stackoverflow.com/questions/62966737/how-to-find-the-multiple-keys-in-multiple-json-and-return-only-those-rows-that-a

1条答案

按热度按时间

nmpmafwu1#

对于案例1，只需将其放入where子句中：

select application_id, 
       processing_json->'official_form_attributes' ->> '81488' 
from schm_ka.processing_data_json 
where application_id = 9356416
  and processing_json -> 'official_form_attributes' ? '81488';

这个 ? 运算符测试json值中是否存在键
对于案例2，您需要聚合：

select application_id, 
       max(processing_json->'official_form_attributes'->>'81488') as key_value_1,
       max(processing_json->'official_form_attributes'->>'81315') as key_value_2 
from schm_ka.processing_data_json 
where application_id = 9356416
  and processing_json->'official_form_attributes' ?| array['81488', '81315']
group by application_id;

这个 ?| 操作符测试数组中提供的任何键是否存在于json值中。显然，当您得到多个具有该条件的行时，需要聚合将它们折叠成一行。

赞(0）回复(0）举报 2021-07-24

我来回答

sql—如何在多个json中找到多个键，并仅返回那些在postgresql9.5中具有某些值的行

1条答案

相关问题

热门标签

最新问答