How to "flatten" objects and just retrieve an array of plain objects

SQL++
1

I am sorry if the subject is kind of confusing, but it’s pretty simple: when I make a query I am getting the results in the format of:

[
  {
    "registrations": {
      "product": "Note 1",
      "user_id": "users::34"
    }
  },
  {
    "registrations": {
      "product": "Note 2",
      "user_id": "users::34"
    }
  },
  {
    "registrations": {
      "product": "Note 3",
      "user_id": "users::34"
    }
  }
]

However, I want the result to be in the format of:

[
  {
    "product": "Note 1",
    "user_id": "users::34"
  },
  {
    "product": "Note 2",
    "user_id": "users::34"
  },
  {
    "product": "Note 3",
    "user_id": "users::34"
  }
]

How can I change the query to make this possible?

Details:
One (of the many) source documents has this signature:

{
  "id": "properties::1234",
  "name": "My house",
  "icon": "house.png",
  "locations": [
    {
      "name": "My room",
      "icon": "bedroom.png",
      "registrations": [
        {
          "product": "samsung note 8",
          "user_id": "users::33"
        },
        {
          "product": "samsung note 9",
          "user_id": "users::33"
        }
      ]
    }
  ],
  "channels": ["properties::1234"],
  "addProducts": ["users::33"],
  "editLocations": ["users::33"],
  "addUsers": ["users::33"],
  "admin": ["users::33"]
}

And I am using this query to get to the result I want to “flatten”:

SELECT registrations FROM properties
UNNEST locations
UNNEST locations.registrations

Any help would be appreciated. Thanks!

1 Like
2 
SELECT r.* FROM properties AS p
UNNEST p.locations AS l 
UNNEST l.registrations AS r
2 Likes
3

This is not what he asked

4

Hello. Yeah, this was not specifically what I asked, but it was more than enough to indicate me the correct way to implement what I was looking for!

5

You want to in the expression , use ARRAY v. registrations FOR v IN a1 END
a1 is the array of objects.