如何使用 PostgresQL 選擇子集 JSON？

我正在嘗試處理沒有顯式 JSON 模式或約定的 JSON 列。JSON 中有不同的鍵。一些鍵不存在，一些鍵確實存在但空字元串值。

例如：

CREATE TABLE json_table (
   id         int,
   json_data   json
);

INSERT INTO json_table (id, json_data) VALUES
 (1, '{"foo" : "bar", "baz": "biz"}');
 (2, '{"foo" : "", "baz": "biz"}');
 (3, '{"hello" : "world"}');
 (4, '{"hello" : "world2", "foo" : "bar2", "baz" : "" }');

1. 是否可以查詢此表並選擇/生成json_data僅包含非空字元串的鍵值對的子集？
2. 是否可以查詢此表並選擇包含給定列表中的 JSON 鍵的記錄？（我基本上想找到json_data鍵為“foo”的記錄）

對於#2，我確實有一個解決方案，它只選擇json_data鍵在正則表達式模式內的記錄：

SELECT tmp.* 
FROM (
   SELECT id, 
          ARRAY(SELECT json_object_keys(json_data))::text AS keys
   FROM json_table
) tmp 
WHERE tmp.keys LIKE ANY(ARRAY['%foo%', '%bar%']);

這產生：

"id","keys"
1,"{foo,baz}"
2,"{foo,baz}"
4,"{hello,foo,baz}"

但有一個警告！我正在返回確實有鍵的記錄，但是空字元串值！有沒有辦法將#1 的解決方案合併到此中並將 JSON 預過濾為僅“有效”鍵值對？

您可以在聚合期間刪除空值。並在最終選擇中使用數組運算符來僅獲取包含特定鍵的行：

SELECT tmp.* 
FROM (
 SELECT id, 
        ARRAY(SELECT t.k
              from json_each_text(json_data) as t(k,v)
              where nullif(trim(t.v),'') is not null) AS keys
 FROM json_table
) tmp 
WHERE keys && array['foo','bar']
;

如果你想要完整的 JSON 值，而不僅僅是鍵，你可以使用這樣的東西：

SELECT tmp.* 
FROM (
 SELECT id, 
        (SELECT jsonb_object_agg(t.k, t.v)
              from json_each_text(json_data) as t(k,v)
              where nullif(trim(t.v),'') is not null) AS keys
 FROM json_table
) tmp 
WHERE keys ?| array['foo','bar']
;

（請注意，在這種情況下，我使用jsonb的是結果，因為它處理起來更加靈活。總的來說，這些天建議更喜歡 jsonb 而不是 json）

---

如果您需要經常清理 json，您可能需要考慮為此編寫一個函式：

create function non_empty_values(p_input jsonb)
 returns jsonb
as
$$
 SELECT jsonb_object_agg(t.k, t.v)
 from jsonb_each(jsonb_strip_nulls(p_input)) as t(k,v)
 where trim(t.v::text) <> '';
$$
language sql
immutable;

引用自：https://dba.stackexchange.com/questions/265731