Unstructed data field: Query all values from array of objects by key 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-&gt;'address'-&gt;'city' as city FROM person

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


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

+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vehicles                                                                                                                                                         |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| [ { "brand": "Toyota", "model": "Corolla", "plate": "AAA-1111", "color": "Red" }, { "brand": "Ford", "model": "Focus", "plate": "ZZZ-9999", "color": "Blue" } ]  |
| &lt;null&gt;                                                                                                                                                           |
| [ { "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-&gt;personalVehicle-&gt;0-&gt;model as model from person

+-------------+
|   model     |
|-------------|
| "Toyota"    |
| &lt;null&gt;      |
| "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?
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-&gt;'personalVehicle')-&gt;&gt;'model' as cars
  from t
) subq
group by id


Output:

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


Here's a fiddle.