0

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?

  • 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} – Jhonatan Cruz May 29 at 16:33
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.