Unstructed data field: Query all values from array of objects by key

Posted on

Question :

I have a table that has a unique ID, and a second ‘collumn’ named ‘data’ that contains simple key/value items like:

"nickname": "value"
"fullName":  "value"
"office": "value"
"unity":  "value"

and a few, more elaborated structure items like:

"address":
    {
        "city": "value",
        "state": "value",
    },

and

"personalVehicle":
    [
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"             
        },
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"             
        }               
    ]

Where, as you can see, personalVehicle is a key that stores an array of objects, in which every object has it’s own simple key/value items.

I can query specific key values from address for all registries: SELECT data->'address'->'city' as city FROM person

+------------+
|    city    |
|------------|
| "city1"    |
| "city2"    |
| "city3"    |
+------------+

Here is the situation: I can query all info about the vehicles with SELECT data->'personalVehicle' as vehicles FROM person

+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vehicles                                                                                                                                                         |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| [ { "brand": "Toyota", "model": "Corolla", "plate": "AAA-1111", "color": "Red" }, { "brand": "Ford", "model": "Focus", "plate": "ZZZ-9999", "color": "Blue" } ]  |
| <null>                                                                                                                                                           |
| [ { "brand": "Hyundai", "model": "Tucson", "plate": "ABC-1212", "color": "Grey" } ]                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

But I cannot retrieve an specific key for all objects, when the objects are inside of an array; in which case, I need to specify the index: SELECT data->personalVehicle->0->model as model from person

+-------------+
|   model     |
|-------------|
| "Toyota"    |
| <null>      |
| "Hyundai"   |
+-------------+

This guy up here, is the first index of the array, that is, the first car. I need to get the models for all N number of cars that the person might have.

How do I do that? Query that without specifying the index?

Answer :

Grab the elements from the json array with json_array_elements and reassemble them with json_agg:

select id, json_agg(cars) from (
  select 
    id,
    json_array_elements(data->'personalVehicle')->>'model' as cars
  from t
) subq
group by id

Output:

1   ["Toyota", "Honda"]
3   ["Chevrolet", "Ford"]
2   ["Renault", "Citroën"]

Here’s a fiddle.

Leave a Reply

Your email address will not be published. Required fields are marked *