Querying JSONB using Postgres

Question

I am attempting to get an element in my JSON with a query.

I am using Groovy, Postgres 9.4 and JSONB.

Here is 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 want to fetch the individual elements in the series array by their ID, I am currently using this query below

select content->'playersContainer'->'series' from site_content 
where content->'playersContainer'->'series' @> '[{"id":"1"}]';

However this brings me back me back both the element with an id of 1 and 2

Below is what I get back

"[{"id": "1", "data": [3, 2, 3, 5, 6], "name": "Nick", "assists": 17, "ratings": [1, 5, 6, 9], "teamName": "Shire Soldiers", "totalGoals": 19, "cleanSheets": 1, "manOfTheMatches": 20}, {"id": "2", "data": [3, 5, 7, 9, 10], "name": "Pasty", "assists": 25, "r (...)"

Can anyone see where I am going wrong? I have seen some other questions on here but they don't help with this.

Answer 1

content->'playersContainer'->'series' is an array. Use jsonb_array_elements() if you want to find a specific element in an array.

select elem
from site_content,
lateral jsonb_array_elements(content->'playersContainer'->'series') elem
where elem @> '{"id":"1"}';

Test it here.