Query JSON in Postgres

Question

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.

score 3 · Accepted Answer · edited May 23 '17 at 11:45

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.

Thanks for the reply however I get this error ERROR: operator does not exist: json @> unknown — N P, Jan 17 '17 at 10:46
I cast my content to jsonb instead of json and it worked, thank you! — N P, Jan 17 '17 at 10:57
Sorry it didn't work, using this query select content->'playersContainer'->'series' from site_content where content->'playersContainer'->'series' @> '[{"id":"1"}]'; I get back by players with an id of 1 and 2, I just want 1 — N P, Jan 17 '17 at 11:06
@NickPocock, it seems here is the answer that might be helpful. In the second column in returns the seeked series element. Here's how it goes: select elem from json_test t, jsonb_array_elements(t.content->'playersContainer'->'series'‌) as elem where elem->>'id'='1' ; — Opal, Jan 17 '17 at 11:43

score -1 · Answer 2 · answered Mar 15 '18 at 19:52

-1

May be it could help: Into the sql statement, I added this 'cast' where I have the json field:

INSERT INTO map_file(type, data)
VALUES (?, CAST(? AS json))
RETURNING id

the datatype of 'data' into map_file table is: json

answered Mar 15 '18 at 19:52

Alejandro Hdz

143
4

You just copy-pasted your answer from [here](https://stackoverflow.com/a/49307794/119775)?... – Jean-François Corbett Mar 15 '18 at 20:15

Query JSON in Postgres

2 Answers2