在查询中获取裁剪的JSON

Question

我在db中有一个列，它对JSON值进行约束，如：

{"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"}

通过查询比如..。

SELECT column->>'key-1' FROM table;

我可以拿到我的val-1。

是否有一种方法可以从已经存在的value值中将 key作为JSON在sql查询中获得？

我想得到这样的结果：

{"key-1": "val-1"}

从…

{"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"}

使用sql查询。

Answer 1

我唯一能想到的就是获取键/值对，并将其组装回一个JSON值：

select jsonb_build_object(j.k, j.v)
from the_table t, jsonb_each(t.json_col) as j(k,v)
where j.k = 'key-1'
  and ... more conditions ...;

在线示例：https://rextester.com/VGSX43955

Answer 2

使用符号运算符，&，例如，

现场测试：https://www.db-fiddle.com/f/9izCEH75JhwVDvsGvsZomG/0

with the_table as
(
    select '{"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"}'::jsonb as d
)
select d & 'key-1' as j from the_table

输出：

| j                 |
| ----------------- |
| {"key-1":"val-1"} |

)创建一个函数，提取所需的键值对，然后为它创建自己的用户定义操作符。

create or replace function extract_one_jsonb(j jsonb, key text)
returns jsonb
as
$$ 
    select jsonb_build_object(key, j->key)
$$ language sql;



create operator & (
  leftarg = jsonb,
  rightarg = text, 
  procedure = extract_one_jsonb
);            

当然，您可以只使用一个函数，或者如果创建一个用户定义的操作符不是一个选项：

with the_table as
(
    select '{"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"}'::jsonb as d
)
select extract_one_jsonb(d, 'key-1') as j from the_table

输出：

| j                 |
| ----------------- |
| {"key-1":"val-1"} |

如果要多次从jsonb中提取密钥值对，则需要为其提供一个操作符，例如&。当您想要创建自己的操作符时，Postgres非常灵活，它也可以创建：->>>。

现场测试：https://www.db-fiddle.com/f/9izCEH75JhwVDvsGvsZomG/1

create operator ->>> (
  leftarg = jsonb,
  rightarg = text, 
  procedure = extract_one_jsonb
);            

输出：

| j                 |
| ----------------- |
| {"key-1":"val-1"} |

->>已经被Postgres：https://www.postgresql.org/docs/11/functions-json.html所使用

您可以创建'->>>'。->>>看起来更像是一个提取器操作符，而不是符号和&。此外，即使您将它粘贴到源字段(即没有空格)，它看起来也不错。

with the_table as
(
    select '{"key-1": "val-1", "key-2": "val-2", "key-3": "val-3"}'::jsonb as d
)
select d->>>'key-1' as j from the_table

尝试了以下操作，它也能工作，看起来像剪刀(用于切割)：%>

select d%>'key-1' as j from the_table