在Postgres中查询JSON [英] Query JSON in Postgres

问题描述

我在postgres数据库中有一些JSON，它在一个名为 site_content 的表中，该表有两行， id 和内容，位于内容中是我存储我的JSON的地方。我希望能够找到一名玩家，因为这是从JSON创建我的图表所需的关键，我的玩家被存储在系列系列的关键字下。

以下是我目前使用的查询：

  Blocking。获取{
 sql.firstRow（SELECT * from site_content where content  - >'playersContainer' - >'series' - >'id'= $ {id}）
 } .map {row  - > 
 log.info（row is：$ {row}）
 if（row）{
 objectMapper.readValue（row.getAt（0）.toString（），Player）
 
 
 $ b $ / code $ / pre 
 
 
然而，我得到这个错误： / b> 
 
 
 
 
 org.postgresql.util.PSQLException：错误：运算符不存在：
 json =字符变化提示：没有运算符匹配给定名称
和参数类型（s）。您可能需要添加明确的类型转换。
 
 
以下是我的JSON示例：
 id：$ {ID}，
团队：{
id：123，
 name：Shire Soldiers
}，
playersContainer：{
series：[
 {
id：1，
name：Nick，
teamName：Shire Soldiers，
rating：[
 1，
 5，
 6 ，
 9 
]，
助攻：17，
manOfTheMatches：20，
cleanSheets：1，
data： [
 3，
 2，
 3，
 5，
 6 
]，
总目标：19 
} ，
 {
id：2，
name：Pasty，
teamName：Shire Soldiers，
ratings：[
 6，
 8，
 9，
 10 
]，
助攻：25，
manOfTheMatches：32，
 cleanSheets：2，
data：[
 3，
 5，
 7，
 9，
 10 
]， 
totalGoals：24 
} 
] 
} 
  
我为这个项目使用Groovy，但我想这只是一般的JSON postgres语法，我遇到了问题。
        p>你说得对，这是SQL语法的问题。正确的查询： 
 
 
  select * from json_test where content->'playersContainer' - >'series'@> [{ ID 为 1}]; 
  
完整示例： 
 
 
  CREATE TABLE json_test（
 content jsonb 
）; 
 
 insert into json_test（content）VALUES（'{id：1，
team：{
id：123，
name：Shire Soldiers
}，
playersContainer：{
series：[
 {
id：1， 
name：Nick，
teamName：Shire Soldiers，
ratings：[
 1，
 5，
 6，
 9 
]，
助手：17，
manOfTheMatches：20，
cleanSheets：1，
data ：[
 3，
 2，
 3，
 5，
 6 
]，
总目标：19 
 }，
 {
id：2，
name：Pasty，
teamName：Shire Soldiers，
 ：[
 6，
 8，
 9，
 10 
]，
 辅助：25，
manOfTheMatches：32，
cleanSheets：2，
data：[
 3，
 5，
 7，
 9，
 10 
]，
总目标：24 
} 
] 
}}'）; 
 
 select * from json_test where content->'playersContainer' - >'series'@> [{ ID 为 1}]; 
  
关于  @>  运算符。此问题也可能有用。
 
I have some JSON in my postgres DB, it's in a table called site_content, the table has two rows, id and content, in content is where I store my JSON. I want to be able to find the a player given his id, my players are stored under the key series as this is the key needed to create my charts from JSON. 

Here is the query I am currently using:
Blocking.get {
                sql.firstRow("""SELECT * from site_content where content -> 'playersContainer' -> 'series' -> 'id' = ${id} """)
            }.map { row ->
                log.info("row is: ${row}")
                if (row) {
                    objectMapper.readValue(row.getAt(0).toString(), Player)
                }
            }
        }
However I get back this error: 

  
org.postgresql.util.PSQLException: ERROR: operator does not exist:
  json = character varying   Hint: No operator matches the given name
  and argument type(s). You might need to add explicit type casts.
Here is an example of my JSON:
"id": "${ID}",
    "team": {
        "id": "123",
        "name": "Shire Soldiers"
    },
    "playersContainer": {
        "series": [
            {
                "id": "1",
                "name": "Nick",
                "teamName": "Shire Soldiers",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19
            },
            {
                "id": "2",
                "name": "Pasty",
                "teamName": "Shire Soldiers",
                "ratings": [
                    6,
                    8,
                    9,
                    10
                ],
                "assists": 25,
                "manOfTheMatches": 32,
                "cleanSheets": 2,
                "data": [
                    3,
                    5,
                    7,
                    9,
                    10
                ],
                "totalGoals": 24
            }
        ]
    }
I am using Groovy for this project, but I guess it's just the general JSON postgres syntax I am having problems with.
解决方案
You're right, that's a problem with SQL syntax. Correct you query:
select * from json_test where content->'playersContainer'->'series' @> '[{"id":"1"}]';
Full example:
CREATE TABLE json_test (
  content jsonb
);

insert into json_test(content) VALUES ('{"id": "1",
    "team": {
      "id": "123",
      "name": "Shire Soldiers"
    },
    "playersContainer": {
      "series": [
        {
          "id": "1",
          "name": "Nick",
          "teamName": "Shire Soldiers",
          "ratings": [
            1,
            5,
            6,
            9
          ],
          "assists": 17,
          "manOfTheMatches": 20,
          "cleanSheets": 1,
          "data": [
            3,
            2,
            3,
            5,
            6
          ],
          "totalGoals": 19
        },
        {
          "id": "2",
          "name": "Pasty",
          "teamName": "Shire Soldiers",
          "ratings": [
            6,
            8,
            9,
            10
          ],
          "assists": 25,
          "manOfTheMatches": 32,
          "cleanSheets": 2,
          "data": [
            3,
            5,
            7,
            9,
            10
          ],
          "totalGoals": 24
        }
      ]
    }}');

select * from json_test where content->'playersContainer'->'series' @> '[{"id":"1"}]';
About @> operator. This question might be also useful.

                        
这篇关于在Postgres中查询JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！