0

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
PuffedRiceCrackers
  • 725
  • 6
  • 18

1 Answers1

0

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
Mathias Kemeter
  • 933
  • 2
  • 11