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"]