PostgreSQL perform queries in JSON array sub-documents

A couple of days ago we had to migrate a large data set from a MongoDB to a PostgreSQL database. The customer did not want to maintain a MongoDB cluster and wanted (yesterday as usual) to move all its data to a new PostgreSQL database.

Since we did not have the available time to build a new relational schema, we had to use the new PostgreSQL JSON capabilities in order to store the existing MongoDB database documents (entries) into a relational table.

Therefore, let us say that our data in MongoDB vehicle collection look like the following :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45


[
    {
        "name": "Toyota Yaris",
        "creation": 2007,
        "owners": [
            {
                "name": "Christos",
                "surname": "Manios",
                "percentage": 50
            },
            {
                "name": "Telis",
                "surname": "Skarpelis",
                "percentage": 50
            }
        ]
    },
    {
        "name": "Toyota RAV4",
        "creation": 2017,
        "owners": [
            {
                "name": "Christos",
                "surname": "Manios",
                "percentage": 70
            },
            {
                "name": "Soulis",
                "surname": "Delapongos",
                "percentage": 30
            }
        ]
    },
    {
        "name": "Ferarri F40",
        "creation": 1999,
        "owners": [
            {
                "name": "Bobos",
                "surname": "Peskardelis",
                "percentage": 100
            }
        ]
    }
]

We can quickly test it in PostgreSQL by creating the following table and inserting some data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- create a test table
CREATE TABLE vehicle (
	id serial NOT NULL PRIMARY KEY,
	info jsonb NOT NULL
);

-- add an index to assist jsonb queries
CREATE INDEX vehicle_info_gin_idx ON vehicle
USING gin (info);


-- insert three vehicles
insert into vehicle (info) values ('
{"name":"Toyota Yaris","creation":2007,"owners":[{"name":"Christos","surname":"Manios","percentage":50},{"name":"Telis","surname":"Skarpelis","percentage":50}]}');
insert into vehicle (info) values ('{"name":"Toyota RAV4","creation":2017,"owners":[{"name":"Christos","surname":"Manios","percentage":70},{"name":"Soulis","surname":"Delapongos","percentage":30}]}');
insert into vehicle (info) values ('{"name":"Ferarri F40","creation":1999,"owners":[{"name":"Bobos","surname":"Peskardelis","percentage":100}]}');

Then let us construct the query which checks if there is a vehicle which belongs to Christos Manios and is the owner with percentage more than 40% .

1
2
3
4
5
6
7


SELECT v.info ->> 'name' AS vehicleType ,
       obj ->> 'name' as ownerName,
       obj ->> 'surname' as ownerName,
       obj ->> 'percentage' as ownerPercentage
from vehicle v, jsonb_array_elements(v.info#>'{owners}') obj
where obj ->> 'surname' = 'Manios'
      and obj ->> 'percentage' > '40' ;

The expected result is the following:

1
2
3
4


| vehicletype  | ownername | ownername | ownerpercentage |
|--------------|-----------|-----------|-----------------|
| Toyota Yaris | Christos  | Manios    | 50              |
| Toyota RAV4  | Christos  | Manios    | 70              |

I hope that you got a little taste of how you can query JSON data in PostgreSQL with SQL!

Greetings from a rainy Thessaloniki!