How to select query from json in Postgres

Question

I have JSON data in field hotel_data like this:

{ 
   "title":"foo",
   "description":[ 
      { 
         "locale":"pt",
         "content":"pt text"
      },
      { 
         "locale":"fr",
         "content":"fr text"
      }
   ]
}

I would like to select description only fr description. It is possible using Postgres and how?

I was trying use ->> but it is not working...

SELECT  
    hotel_data->'description'->>'locale' = 'fr' AS description
FROM hotel LIMIT 1;

Note: I don't want to use SELECT *...

Excepted output: {description: "fr text"}

Answer 1

The filtering can be done using the @> operator which can use a GIN index on the hotel_data column. This is typically faster than expanding the array.

select ...
from hotel
where hotel_data @> '{"description": [{"locale":"fr"}] }';

This can also be extended to include more properties:

select ...
from hotel
where hotel_data @> '{"description": [{"locale":"fr", "headline": "nice view'}] }';

But you can only express equality conditions on the key/value pairs with that. Using LIKE is not possible. You will have to expand the array if you want to do that and apply the condition in the WHERE clause - see GMB's answer.

To extract that description, I would use a scalar sub-query:

select (select jsonb_build_object('description', t.descr ->> 'content')
        from jsonb_array_elements(h.hotel_data -> 'description') as t(descr)
        where t.descr ->> 'locale' = 'fr'
        limit 1)
from hotel h
where h.hotel_data @> '{"description": [{"locale":"fr"}] }';

That way you don't need to expand the array for filtering which I expect to be faster if only a few hotels qualify for that condition. But it has the drawback that you need to repeat the condition on the locale in the sub-select.

The limit 1 is only a safety net in case you have more than one french description. If you never have that, it doesn't hurt either

---

With Postgres 12 this is easier:

select jsonb_build_object(
         'description', 
         jsonb_path_query_first(hotel_data, '$.description ? (@.locale == "fr")') -> 'content'
       )
from hotel
where hotel_data @> '{"description": [{"locale":"fr"}] }'

---

All of the above assumes hotel_data is a jsonb column, if it's not (which it should be) you need to cast it: hotel_data::jsonb

Answer 2

You can use a lateral join and json_to_recordset to expand the json array as a set of records. Then, you can filter on column locale in the generated records, and finally recompose a new json object with your expected result:

select json_build_object('description', d.content) hotel_data_descr_fr
from 
    mytable,
    json_to_recordset(hotel_data->'description') as d("locale" text, "content" text)
where d.locale = 'fr'

Demo on DB Fiddle:

with mytable as (
    select '{ 
   "title":"foo",
   "description":[ 
      { 
         "locale":"pt",
         "content":"pt text"
      },
      { 
         "locale":"fr",
         "content":"fr text"
      }
   ]
}'::json hotel_data
)
select json_build_object('description', d.content) hotel_data_descr_fr
from 
    mytable,
    json_to_recordset(hotel_data->'description') as d("locale" text, "content" text)
where d.locale = 'fr'

| hotel_data_descr_fr        |
| :------------------------- |
| {"description": "fr text"} |