PostgreSQL With JSON Story-01

This article is primarily concerned with doing some PostgreSQL querying with JSON values.

Hence, to understand this, a basic idea for the query syntax of PostgreSQL is needed.

Here, the main goal is to have a look at sql queries while having JSON values with complex data types.

To create the example scenario, a table will be used having a structure as mentioned below.

id integer Not NULL primary key

firstname character varying (100) Not NULL

lastname character varying (100) Not NULL

details json Not NULL

CREATE TABLE person( id INT PRIMARY KEY, firstname VARCHAR(100) NOT NULL, lastname VARCHAR(100) NOT NULL, details JSON NOT NULL);

Here, the column called details is the one that stores json values.

As an example, I am going to use json values as mentioned below.

{
  "email": "yasas@gmail.com",
  "phoneNumber1": "+94111111111",
  "phoneNumber2": "+44111111111",
  "address": {
    "no": "56",
    "street": "json streat",
    "city": "Maharagama",
    "country": "Sri Lanka"
  },
  "locations": [
    {
      "code": "LK",
      "name": "Sri Lanka",
      "lat": 128.12,
      "lon": 138.23
    },
    {
      "code": "GB",
      "name": "United Kingdom",
      "lat": 148.12,
      "lon": 158.23
    },
    {
      "code": "IND",
      "name": "India",
      "lat": 163.12,
      "lon": 172.23
    }
  ]
}

And some example data can be added to the table, so we can test various queries with JSON values. As for the demonstrations, I have inserted these example values into the table.

INSERT INTO person(id, "firstname", "lastname", details) VALUES (1, 'Yasas', 'Ranawaka', '{"email":"example@gmail.com","phoneNumber1":"+94111111111","phoneNumber2":"+44111111111","locations":[{"code":"LK","name":"Sri Lanka","lat":128.12,"lon":138.23},{"code":"GB","name":"United Kingdom","lat":148.12,"lon":158.23},{"code":"IND","name":"India","lat":163.12,"lon":172.23}]}' );

As It is mentioned earlier, the main target here is the column called details, which stores values in JSON format.

In the JSON, we have these fields,

email → string phoneNumber1 → string phoneNumber2 → string address → object { no → string street → string city → string country → string } locations [ → array of objects { code → string name → string lat → number lon → number } ]

If you refer to PostgreSQL documentation, you can get the idea about the JSON operators and functions.

01. For a start, think about how we are going to select rows filtering by email in JSON.

The query is simple like this,

SELECT * FROM person WHERE details->>'email' ILIKE '%si%';

Notes: You can also select the json field from “ SELECT details->>’email’ “ and ILIKE is the same as LIKE but case-insensitive.

02. Now how we are going to filter from the address field. It is not a primitive data type field and it is an object.

It is also simple like mentioned below. First we can go with the object field name which is “address” and then we can get the field name inside of the object field.

SELECT details->>'address' FROM person WHERE details->'address'->>'street' ILIKE '%js%';

03. But consider the location field in the JSON values. It is not also a primitive data type. It is an array of objects.

locations: [ 
  { 
    "code": "LK",
    "name": "Sri Lanka", 
    "lat": 128.12, 
    "lon": 138.23 
  }, 
  { 
    "code": "GB",
    "name": "United Kingdom", 
    "lat": 148.12, 
    "lon": 158.23 
  } 
]

We can’t filter as before when it is an array. Consider below filtering query.

SELECT * 
FROM person 
WHERE details->'locations'->0->>'code' ILIKE '%lk%';

In this query, it filters only the first element in the array from each row. Because it is already hard-coded for the first index.

A. If your requirement is to filter with only first element in location array, this is the answer.

B. If your requirement is to filter with only the last element in the location array, you can use a JSON processing function called json_array_length which gives the size of the json array and then reduces by 1.

SELECT details->>'locations' 
FROM person 
WHERE details->'locations'->json_array_length(details->'locations')-1 ->>'code' ILIKE '%IND%';

C. If you need to filter without mentioning any special index, how to do it.

For this, we have to look at a JSON processing function called json_array_elements.

Consider below two queries,

SELECT details->'locations' 
FROM person;
SELECT json_array_elements(details->'locations') 
FROM person;

You can clearly see that the function json_array_elements has expanded the locations of the JSON array to a set of JSON values.

From this way, data can be filtered as we wanted.

Let’s consider the below query.

SELECT * 
FROM person 
CROSS JOIN json_array_elements(person.details->'locations') AS locations;

You know that CROSS JOIN returns all records from both tables like the Cartesian Product.

Now we can use the DISTINCT key to get only distinct values.

SELECT DISTINCT((person.details)::jsonb), person.id, person.firstname, person.lastname 
FROM person 
CROSS JOIN json_array_elements(person.details->'locations') AS locations;

Then we can filter early using operators from the JSON array like below.

SELECT DISTINCT((person.details)::jsonb), person.id, person.firstname, person.lastname 
FROM person 
CROSS JOIN json_array_elements(person.details->'locations') AS locations 
WHERE locations->>'code' IN ('LK','GB');

Like this we can do querying step by step.

Yasas Ranawaka

Originally published at https://www.linkedin.com.