如何仅从Postgres获取特定键的jsonb？ [英] How to get only the jsonb of specific keys from postgres?

问题描述

我知道您可以使用以下方法从Postgres中的jsonb中删除密钥

I'm aware that you can remove keys from a jsonb in postgres using something like this

select '{"a": 1, "b": 2, "c":3}'::jsonb -'a';
 ?column?
----------
{"b": 2 "c":3}
(1 row)

有没有办法只抓住特定的钥匙？比如说我只想获取'a'键的键值对

Is there a way to only grab specific keys? Like lets say I just want to get the key-value pair of just the 'a' key

类似

select '{"a": 1, "b": 2}'::jsonb + 'a' + 'b';
 ?column?
----------
{"a": 1, "b": 2}
(1 row)

编辑：更改示例以显示我想从jsonb中获取多个键值对，而不仅仅是一对。

changed the example to to show that I'd like to grab multiple keys-value pairs from the jsonb and not just one pair.

推荐答案

您可以像这样轻松地过滤到单个键：

You can filter down to a single key fairly easily like so:

jsonb_object(ARRAY[key, jsonb_data -> key])

...或者您可以筛选出多个键：

...or you can filter down to multiple keys:

(SELECT jsonb_object_agg(key, value) FROM jsonb_each(jsonb_data) WHERE key IN ('a', 'b'))

或者在更复杂的条件下，如果您想要：

Or on a more complex condition, if you want:

(
  SELECT jsonb_object_agg(key, value)
  FROM jsonb_each(jsonb_data)
  WHERE
    key NOT LIKE '__%'
    AND jsonb_typeof(value) != 'null'
)

只需阅读文档。

这篇关于如何仅从Postgres获取特定键的jsonb？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！