时间戳

首页 > 解决方案 > 更新 jsonb 对象以包含来自父行的键/值

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

问题描述

我有一个带有 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

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

标签： sqljsonpostgresql

解决方案

使用jsonb_build_object()并将结果连接到现有jsonb对象：

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

实际上UPDATE：

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
;

-as 左操作jsonb数和textas 右操作数的运算符定义如下：

从左操作数中删除键/值对或字符串元素。键/值对根据其键值进行匹配。

关于使用的函数和运算符的手册。

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

推荐阅读