Query and get the value from nested JSON in Postgres

Postgresql provides features to do the query on JSON field eg: json_array_elements. Please make your column have JSON format to use these features. Now, often the problem is how to do the query for particular row data that have nested JSON structure inside.

For instance, I have table with structure as below

CREATE TABLE public.raw (
	id bigserial NOT NULL,
	url text NULL,
	response_body json NULL,
	CONSTRAINT raw_pkey PRIMARY KEY (id)
);

And I have data stored in column response_body, with 1 row data:

{
  "response": "Success",
  "data": [
    {
      "cid": "5229",
      "country": "JP",
    },
    {
      "cid": "1002",
      "country": "US",
    },
    {
      "cid": "2004",
      "country": "US",
    },
    {
      "cid": "100",
      "country": "MY",
    },
    {
      "cid": "2999",
      "country": "AG",
    },

Let’s say, I want to show list of “cid”

The problem here, I want to iterate elements in this JSON data and get the values on specific keys. How to do it in Postgresql? All you need just do nested “json_array_elements” (I’m ignoring performance here).

select json_array_elements(response_body->'data')->>'cid' as cid FROM public.raw where id=1

Here are the explanation steps:

1. Access the data keyname using response_body->'data'
2. Wrap this with json_array_elements to produce array output
3. Access the nested key name by using ->> (field name)

If you want to give list of country, then use ->>country. If you want to iterate more, than you can create the nested same as the code above.

Some good references:

https://gist.github.com/sebflipper/e95d12d72492fbab1b08