Select a specific entry in json with PostgreSQL

Question

I have the following JSON which is stored in a jsonb field named "Data" in a PostgreSQL database:

{
    "CompetitionData" :
    {
        "StartDate" : "12.06.2018",
        "Name" : "TestCompetition",
        "Competitors" :
        [
            {
                "Id" : "100",
                "Name" : "John",
                "Age" : "24",
                "Score" : "98",
                "Shoes":
                {
                    "Brand" : "Nike"
                }
            },
            {
                "Id" : "200",
                "Name" : "Adam",
                "Age" : "32",
                "Score" : "78",
                "Shoes":
                {
                    "Brand" : "Adidas"
                }
            }
        ]
    }
}

Im trying to get a specific entry in Competitors, like e.g.

SELECT * FROM Competitors WHERE Shoes = "Nike";

And the result must look like this:

{
       "Id" : "100",
       "Name" : "John",
       "Age" : "24",
       "Score" : "98",
       "Shoes":
       {
           "Brand" : "Nike"
       }
}

I tried the following query, but i keeps returning all competitors:

SELECT jsonb_array_elements(public."Competitions"."Data"->'CompetitionData'->'Competitors') as test
FROM public."Competitions" WHERE public."Competitions"."Data" @> '{"CompetitionData":{"Competitors":[{"Shoes":{"Brand":"Nike"}}]}}';

Is it possible to return just the competitor with Shoe Brand "Nike" ?

Answer 1

Use jsonb_array_elements in the from clause

SELECT j.* FROM
t cross join lateral 
  jsonb_array_elements(data->'CompetitionData'->'Competitors') as j(comp)
where j.comp->'Shoes'->>'Brand' = 'Nike'

Demo