How to load column table data into hdb collection

[minjie_lao]

Hi,

I understood, we can easily generate the json base on 'for json' function. But how can i store the result set into hdb collection?

select 'myvalue' as "key" from dummy for json;

 i tried to use, seems not working

insert into myCollection values (select 'myValue' as "key" from dummy for json);

[Vitaliy-R · Accepted solution]

You did not write what issues you faced, but for me to get it working I needed to use

insert into "myCollection" (SELECT 'myvalue' AS "key" FROM dummy FOR JSON ('arraywrap'='no'));

1/ You do not need `VALUES` when inserting from `SELECT`: https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-json-document-store-g...

2/ The result of your `SELECT` is 

[{"key":"myvalue"}]

which cannot be inserted per documentation:

It is not possible to insert multiple documents with a single string, like a string that contains an array of documents.

so I used json_option_string_list 

('arraywrap'='no')

to remove `[]`.

I hope this helps.
--Vitaliy

[minjie_lao · Accepted solution]

thanks Vitaliy, it works