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" ?

Kaushik Nayak · Accepted Answer · 2019-03-08 11:15:36Z

2

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

answered Mar 8, 2019 at 11:15

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Lorenz Henk · Accepted Answer · 2019-03-08 10:52:48Z

0

Why do you want to save it in jsonb? Just normalize it into a database:

CREATE TABLE competitor_shoe (
  name  text PRIMARY KEY,
  brand text NOT NULL
);

CREATE TABLE competitor (
  id    int  PRIMARY KEY,
  name  text NOT NULL,
  age   int  NOT NULL,
  score int  NOT NULL,
  shoe  text NOT NULL REFERENCES competitor_shoe(name)
);

CREATE TABLE competition (
  name       text PRIMARY KEY, 
  start_date date NOT NULL
);

CREATE TABLE competition_competitor (
  competition text REFERENCES competition,
  competitor  int  REFERENCES competitor,
  PRIMARY KEY (competition,competitor)
);


INSERT INTO competitor_shoe
VALUES ('shoes1', 'Nike'), 
  ('shoes2', 'Adidas');

INSERT INTO competitor 
VALUES (100,'John',24,98,'shoes1'),
  (200,'Adam',32,78,'shoes2');

INSERT INTO competition 
VALUES (
  'TestCompetition',
  '12.06.2018'
);

INSERT INTO competition_competitor
VALUES ('TestCompetition', 100), ('TestCompetition', 200);


-- query the data
SELECT * 
FROM   competitor c
JOIN   competitor_shoe cs
ON     c.shoe = cs.name
WHERE  brand = 'Nike';

-- query the data and return it as json object
SELECT to_jsonb(c) || jsonb_build_object('shoe', to_jsonb(cs)) as data
FROM   competitor c
JOIN   competitor_shoe cs
ON     c.shoe = cs.name
WHERE  brand = 'Nike';

answered Mar 8, 2019 at 10:52

Lorenz Henk

7815 silver badges13 bronze badges

1 Comment

Black Ops Over a year ago

Because i have to store it in jsonb, due to other requirements

Collectives™ on Stack Overflow

Select a specific entry in json with PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related