sql - 更新 jsonb 对象以包含来自父行的键/值

Question

我有一个包含 id 列和 jsonb 列的表。

我想更新每一行的 jsonb 对象以包含“父”行的 id 值。

像这样创建我的设置:

CREATE TABLE people ( 
    id serial PRIMARY KEY,
    meta jsonb
);


insert into people (meta) values 
    ('{"age": 10}'),
    ('{"age": 15}'),
    ('{"age": 20}')
;

结果是:

id  meta
1   {"age": 10}
2   {"age": 15}
3   {"age": 20}

我想迁移数据，使其看起来像这样:

id  meta
1   {"id": 1, "age": 10}
2   {"id": 2, "age": 15}
3   {"id": 3, "age": 20}

Postgres 版本:10.7

我使用子查询有点接近，但它不是很有效。感谢您的帮助!

Answer 1

使用 jsonb_build_object() 然后将结果连接到现有的 jsonb 对象:

SELECT meta || jsonb_build_object('id', id)
FROM people;

实际更新:

UPDATE people
SET meta = meta || jsonb_build_object('id', id);

通常，将 ID 冗余地存储在 JSON 对象中没有多大意义。但你似乎有你的理由。

相反，从 jsonb 中删除一个(顶级!)键甚至更简单:

SELECT meta - 'id' FROM people;

或者:

UPDATE people
SET    id = (meta->>'id')::int  -- to set id
     , meta = meta - 'id'       -- to *also* remove from jsonb
;

- 作为左操作数的 jsonb 和作为右操作数的 text 运算符是 defined as follows :

Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.

The manual on the used function and operators.

如果没有名为“id”的键，或者您得到的是 NULL - 这对于 notnull 列是不合法的...