如何仅从postgres json中选择特定键

您可以明确指定键,如下所示:

t=# with c(j) as (SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' - 'ddd}')
select j,jsonb_build_object('aaa',j->'aaa','bbb',j->'bbb') from c;
                    j                     |  jsonb_build_object
------------------------------------------+----------------------
 {"aaa": 1, "bbb": 2, "ccc": 3, "ddd": 7} | {"aaa": 1, "bbb": 2}
(1 row)

WITH data AS (
  SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' col
)
SELECT kv.*
FROM data,
LATERAL (
  SELECT jsonb_object(ARRAY_AGG(keyval.key::TEXT), ARRAY_AGG(keyval.value::TEXT)) 
  FROM jsonb_each(col) keyval
  WHERE keyval.key IN ('aaa', 'bbb', 'ccc')) kv

解决方案通过扩展 JSONB (或 JSON )对象,筛选键,聚合筛选的键(&P);创建最终 JSONB公司 (或 JSON )对象。

但是,此解决方案不保留null,即如果 data 吵架了 col 有价值 jsonb '{"aaa":1,"bbb":2, "ddd":7}' ,则上述解决方案将返回 jsonb '{"aaa":1,"bbb":2}'

要保留空值,可以使用以下形式。

WITH data AS (
  SELECT jsonb '{"aaa":1,"bbb":2,"ccc":3,"ddd":7}' col
), keys(k) AS (
  VALUES ('aaa'), ('bbb'), ('ccc')
)
SELECT col, jsonb_object(ARRAY_AGG(k), ARRAY_AGG(col->>k))
FROM data, keys 
GROUP BY 1