Postgres jsonb嵌套数组追加 [英] Postgres jsonb nested array append

问题描述

我有一个带有jsonb列的简单表

I have simple table with a jsonb column

CREATE TABLE things (
  id SERIAL PRIMARY KEY,
  data jsonb
);

数据如下:

{
    "id": 1,
        "title": "thing",
        "things": [
            {
                "title": "thing 1",
                "moreThings": [
                    { "title": "more thing 1" }
                ]
            }
        ]
}

那我该如何在像moreThings这样的深层嵌套数组中追加呢?

So how do I append inside of a deeply nested array like moreThings?

对于单级嵌套数组，我可以做到这一点，并且有效:

For single level nested array I could do this and it works:

UPDATE posts SET data = jsonb_set(data, '{things}', data->'things' || '{ "text": "thing" }', true);

但是对于深度嵌套的数组而言，这是行不通的:

But the same doesn't work for deeply nested arrays:

UPDATE posts SET data = jsonb_set(data, '{things}', data->'things'->'moreThings' || '{ "text": "thing" }', true)

如何添加到moreThings?

推荐答案

效果很好:

UPDATE things
SET data =
    jsonb_set(data,
              '{things,0,moreThings}',
              data->'things'->0->'moreThings' || '{ "text": "thing" }',
              TRUE
    )
WHERE id = 1;

如果您有一个仅包含主键和jsonb属性的表，并且您经常要在数据库中操作此jsonb，则您肯定做错了.如果对数据进行更多的标准化，将使您的生活变得更加轻松.

If you have a table that consists only of a primary key and a jsonb attribute and you regularly want to manipulate this jsonb in the database, you are certainly doing something wrong. Your life will be much easier if you normalize the data some more.

这篇关于Postgres jsonb嵌套数组追加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！