追加一个json对象到我的postgres表中的一个数组 [英] append a json object to an array within my postgres table

问题描述

我有一个使用Groovy sql存储一些JSON的postgres表格，我想将一个对象添加到我的'players'数组中以将新的播放器添加到我的数据库中。目前我正在尝试这个。我尝试定位我的'玩家'阵列并追加来自服务器的新的json。这给了我一个错误

.PSQLException：错误：函数array_append（jsonb，jsonb）不存在
存在

我想我可能会得到这个错误有第一个参数需要是一个数组而不是jsonb，有没有一种方法来定位我的数据库中的数组所以它回来作为一个数组？或者是否有更好的方式将这个对象追加到我的数组？

  sql.executeUpdate（
 UPDATE site_content 
 SET content = array_append（content->'playersContainer' - >'players'，'$ {json}':: jsonb）; 
其中id =：id 
 ，id：player.teamId）
} 
  

这是我的JSON

$ b $ pre $ playersContainer：{
players：[
{
id：1 ，
name：Nick Pocock，
teamName：Shire Soldiers，
bio：Lorem ipsum dolor sit amet，consectetur adipiscing elit。Nulla imperdiet lorem tellus ，在bibendum sem dignissim sed。Etiam eu elit sit amet lacus accumsan blandit sed ut dolor。Mauris vel dui non nisi vestibulum commodo vel id magna。Donec egestas magna in tincidunt mollis。Fusce maria arcu，rhoncus ut lacus sed，fermentum ultrices elit。在sollicitu在ex dapibus vestibulum喧嚣。 Pellentesque congue，est id lobortis viverra，mauris lectus pharetra orci，ut suscipit nisl purus vehicula est。Aliquam suscipit non velit vel feugiat。 ，
rating：[
1，
5，
6，
9
]，
助手：17，
manOfTheMatches：20，
cleanSheets：1，
data：[
3，
2，
3，
5，
6
]，
总目标：19

}
}

我的列被称为包含JSONb的内容

解决方案 9.5：

  UPDATE site_content 
 SET content = jsonb_set（content，'{playersContainer，players }':: text []，content->'playersContainer' - >'players'||'$ {json}':: jsonb）; 
其中id =：id 
  

I have a postgres table which stores some JSON using Groovy sql, I want to append an object to my 'players' array to add a new player to my database. At the moment I am trying this.I try to target my 'players' array and append my new json which comes from the server. This gives me an error

.PSQLException: ERROR: function array_append(jsonb, jsonb) does not exist

I think I may be getting this error has the first param needs to be an array not jsonb, is there a way to target the array in my db so it comes back as an array? Or is there a better way to append this object to my array?

  sql.executeUpdate("""
            UPDATE site_content
            SET content = array_append(content->'playersContainer'->'players', '${json}'::jsonb);
            where id = :id
        """, id: player.teamId)
}

Here is my JSON

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick Pocock",
                "teamName": "Shire Soldiers",
                "bio" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla imperdiet lorem tellus, in bibendum sem dignissim sed. Etiam eu elit sit amet lacus accumsan blandit sed ut dolor. Mauris vel dui non nisi vestibulum commodo vel id magna. Donec egestas magna in tincidunt mollis. Fusce mauris arcu, rhoncus ut lacus sed, fermentum ultrices elit. In sollicitudin at ex dapibus vestibulum. Pellentesque congue, est id lobortis viverra, mauris lectus pharetra orci, ut suscipit nisl purus vehicula est. Aliquam suscipit non velit vel feugiat. Quisque nec dictum augue.",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            }
}

My column is called content which holds the JSONb

解决方案

For 9.5:

UPDATE site_content
            SET content = jsonb_set(content, '{playersContainer,players}'::text[], content->'playersContainer'->'players' || '${json}'::jsonb);
            where id = :id

这篇关于追加一个json对象到我的postgres表中的一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！