postgresql jsonb query by inner property

Question

I'm using postgresql 10.6. My table has a jsonb column travel filled with below sample data. Below is the sqlfiddle;

My table:

id | travel                                                                                                                                                                                                   
-: | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1 | {"name": "Lucy", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "Paris", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}       
 2 | {"name": "Tom", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Kyoto", "continent": "Asia"}, {"city": "Frankfurt", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}      
 3 | {"name": "Lenny", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "New York", "continent": "America"}, {"city": "Seattle", "continent": "America"}]}

DDL and insert code:

create table people (
    id serial primary key,
    travel jsonb
);

insert into people (travel) values (
'{
    "name": "Lucy",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "Europe",
        "city": "Paris"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Tom",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Kyoto"
      },
      {
        "continent": "Europe",
        "city": "Frankfurt"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Lenny",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "America",
        "city": "New York"
      },
      {
        "continent": "America",
        "city": "Seattle"
      }
    ]
  }
'::jsonb);

How can I query the travels to cities which has "o" letter in it, in continent Asia ?

Thanks & regards

Please include the relevant sample data and table definitions in your question as formatted text. Outside links don't always work (for me SQLFiddle hardly ever works, it always hangs) — user330315
– user330315, Commented Oct 15, 2019 at 6:54

Bergi · Accepted Answer · 2019-10-16 01:31:34Z

1

I think your own answer is just fine. The array selection can be a bit simplified, and the duplication of the continent filter condition is a bit ugly - I would probably write

SELECT *
FROM (
  SELECT
    travel -> 'name' as name,
    ARRAY(
      SELECT mytrips
      FROM jsonb_array_elements(travel -> 'trips') mytrips
      WHERE mytrips ->> 'continent' = 'Europe'
    ) as trips
  FROM
    people
  ) t
WHERE 
  trips <> '{}'

^{(online demo)}
On the other hand, if you do have an index on travel, the @> operator in the WHERE clause might be faster.

Possibly simpler, but with different semantics regarding multiple trips by the same person, would be a grouping approach:

SELECT travel -> 'name' as name, jsonb_agg(trip) as trips
FROM people, jsonb_array_elements(travel -> 'trips') trip
WHERE trip ->> 'continent' = 'Europe'
GROUP BY name

^{(online demo)}

edited Oct 16, 2019 at 1:31

answered Oct 16, 2019 at 1:22

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Comments

S-Man · Accepted Answer · 2019-10-15 07:03:38Z

0

It is not quite clear to me what is your expected output. But to find the cities with o in Asia is like that:

demo:db<>fiddle

SELECT 
    * 
FROM 
    people,
    jsonb_array_elements(travel -> 'trips') elems
WHERE
    elems ->> 'city' LIKE '%o%'
    AND elems ->> 'continent' = 'Asia'

Expand the array elements into one row each
Filter by continent and city

answered Oct 15, 2019 at 7:03

S-Man

24k9 gold badges51 silver badges78 bronze badges

2 Comments

killjoy Over a year ago

S-Man, thanks for your answer, To be more specific, let's ask who travelled to Europe and what are the trips? I'd like to construct the reply as below;

[   {     "name": "Lucy",     "trips": [       {         "continent": "Europe",         "city": "Paris"       },       {         "continent": "Europe",         "city": "London"       }     ]   },   {     "name": "Tom",     "trips": [       {         "continent": "Europe",         "city": "Frankfurt"       },       {         "continent": "Europe",         "city": "London"       }     ]   } ]

S-Man Over a year ago

What about this? dbfiddle.uk/…

killjoy · Accepted Answer · 2019-10-15 21:56:34Z

0

I could obtain the result as I want with the below query. However, I'm not sure if it's optimal in terms of performance. Any suggestion to make it perform better?

SELECT
  travel -> 'name',   
   Array(      
      (SELECT elements.mytrips FROM
       (SELECT jsonb_array_elements(travel -> 'trips') as mytrips) as elements  
       WHERE elements.mytrips ->> 'continent' = 'Europe'
      )     
    )   
FROM 
  people
WHERE 
  travel -> 'trips' @> '[{"continent": "Europe"}]'

answered Oct 15, 2019 at 21:56

killjoy

1,0621 gold badge21 silver badges36 bronze badges

1 Comment

Bergi Over a year ago

Europe or Asia (as in your question)?

Collectives™ on Stack Overflow

postgresql jsonb query by inner property

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related