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.

Community · Accepted Answer · 2017-05-23 11:45:49Z

3

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 17, 2017 at 7:04

Opal

85.5k29 gold badges201 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

N P Over a year ago

Thanks for the reply however I get this error ERROR: operator does not exist: json @> unknown

N P Over a year ago

I am using postgres 9.4

N P Over a year ago

I cast my content to jsonb instead of json and it worked, thank you!

N P Over a year ago

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

Opal Over a year ago

@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' ;

AlejandroHernandez.Inc · Accepted Answer · 2018-03-15 19:52:17Z

-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, 2018 at 19:52

AlejandroHernandez.Inc

1534 bronze badges

1 Comment

Jean-François Corbett Over a year ago

You just copy-pasted your answer from here?...

Collectives™ on Stack Overflow

Query JSON in Postgres

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related