Select objects inside array JSONB

Question

I am attempting to grab the objects within an array using JSONB in a Postgres db and Groovy.

My Json is structured like this, stored in my database.

"playersContainer": {
        "players": [
            {
                "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 grab the objects inside the players array, loop over them map them to object models in my project and store them in a list.

At the moment I use this query:

SELECT content -> 'playersContainer' -> 'players' from site_content

this brings me back

[{"id": "1", "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.", "data": [3, 2, 3, 5, 6], "name": "Nick Pocock", "assists": 17, "ratings": [1, 5, 6, 9], "teamName": "Shire Soldiers", "totalGoals": 19, "cleanSheets": 1, "manOfTheMatches": 20}, {"id": "2", "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.", "data": [3, 5, 7, 9, 10], "name": "Connor Pasty", "assists": 25, "ratings": [6, 8, 9, 10], "teamName": "Shire Soldiers", "totalGoals": 24, "cleanSheets": 2, "manOfTheMatches": 32}]

Which gives me an array of objects however I just want the objects inside I then want to loop over the objects, map them and store them in a list, something like this in Groovy.

    Blocking.get {
            sql.rows("""
            SELECT content -> 'playersContainer' -> 'players' from site_content
              """)
        }.map { rows ->
            rows.collect { GroovyRowResult result ->      
                objectMapper.readValue(result, Player)
            }
        }
    }

is this possible with JSONB?

Hoang Hiep Hoang Hiep · Accepted Answer · 2017-02-01T22:06:09

You can use jsonb_array_elements (read the docs)

SELECT jsonb_array_elements(content->'playersContainer'->'players') FROM site_content

Select objects inside array JSONB

1 Answers