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?
SELECT id,vehicle->>'brand' as brand FROM (SELECT id,json_array_elements(data->'personalVehicle') as vehicle from person) as brandbut that returns me one row at a time; that is, if the person has 3 cars, it will return 3 rows with the same id for that person. 1 - Chevrolet, 1 - Ford, 1 - Hyundai. I'm sure there is a way to get a array for example, containing all brands, making it one row per person:1 - {Chevrolet, Ford, Hyundai}