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

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?

• Jhonatan Cruz: I came to a workaround using a subquery but it's far from what I wanted: SELECT id,vehicle->>'brand' as brand FROM (SELECT id,json_array_elements(data->'personalVehicle') as vehicle from person) as brand but 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}

Answer 1

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.