search

cube2222 / octosql

OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.
Mozilla Public License 2.0
4.75k stars 201 forks source link

Question: how can I convert an array of objects to a table? #266

Closed disintegrator closed 2 years ago

disintegrator commented 2 years ago

Given the follow schema:

+---------+--------------------------+------------+
|  name   |           type           | time_field |
+---------+--------------------------+------------+
| 'items' | '[{closed: Boolean;      | false      |
|         | creationMethod: NULL;    |            |
|         | id: String; idBoard:     |            |
|         | String; limits:          |            |
|         | {cards: {openPerList:    |            |
|         | {disableAt: Float;       |            |
|         | status: String; warnAt:  |            |
|         | Float}; totalPerList:    |            |
|         | {disableAt: Float;       |            |
|         | status: String; warnAt:  |            |
|         | Float}}}; name: String;  |            |
|         | pos: Float; softLimit:   |            |
|         | NULL; subscribed:        |            |
|         | Boolean}]'               |            |
+---------+--------------------------+------------+

I'd like to convert the items column into a table where the object keys become columns. Is this possible in octosql?

cube2222 commented 2 years ago

Hey @disintegrator!

This is not possible right now.

You can use unnest(items) to get one row per array element.

Operations on objects are one of the main things on my radar right now, so this should soon be possible.

disintegrator commented 2 years ago

Thanks for the response @cube2222 :) I'm going to close the ticket as I don't have a need for this feature at this time. Feel free to reopen it if you want to use it to track work.