There is a column that holds a json formatted value.
id| statuses |
--+---------------------------+
1 | { "a": true, "b": false } |
2 | { "c": true } |
Is it possible to turn it into a table as below?
id | statusName | value
---+------------+------------------
1 | a | true
1 | b | false
2 | c | true
I found JSON_TABLE functions (from this document), but its usage seems to be applicable only when you already know the keys but that is not the case. If only I could extract the keys in json there might be a way, but is it possible?
SELECT JT.*
FROM JSON_TABLE(<tableName>.statuses , '$')
COLUMNS
(
VALUE BOOLEAN PATH '$.*'
)
) AS JT; --did not work
In essence you would like to retrieve a table of an unknown format. Also the structure of the table would change with its data (e.g. a new json document could add new columns). This is a bit against the paradigms of a database and makes it hard for clients to work with the result set. I would not assume, that you find a good built-in solution.
However, when working with JSON documents in SAP HANA it may make sense to have a look at HANA's JSON Document Store. You can insert JSON documents into so-called Collections and apply a schema-on-read.
For example the following statement will return all the values and statusNames that exist and return NULL if the corresponding attribute/key does not exist within the document:
SELECT id, statusName, value FROM myDocStoreCollection