CODEWITHSUNDEEP
sqlsql-functionsinglestore
Lukas Pistelak
Lukas Pistelak

Reputation: 1

JSON_KEYS function

in the JSON_KEYS function, i dont understand what is return type:

https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-keys/

according the doc : Return Value: A JSON array

items_keys = JSON_KEYS(weighted_items_json);

i tried declare items_keys the as : items_keys ARRAY(VARCHAR(255)); items_keys ARRAY(INT); items_keys JSON;

every time gets the error:

Y000][2211] Compilation error in function recommendation_groupon_us.new_method near line 40: It is not valid to create a value of type 'array(varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL)' from 'JSON_KEYS(weighted_items_json)' because the types are incompatible

Upvotes: 0

Views: 64

Answers (1)

John Cult
John Cult

Reputation: 431

I think in SingleStore, JSON is treated as a distinct data type, separate from arrays or other data types.1

To work with the result of JSON_KEYS, you should declare items_keys as a JSON type.

For example:

DECLARE items_keys JSON;
SET items_keys = JSON_KEYS(weighted_items_json);

If you need to work with the keys as an array, you might need to use the JSON_TO_ARRAY function to convert the JSON array to a SingleStore array. Here's how you might do that:

DECLARE items_keys_array ARRAY(JSON);
SET items_keys_array = JSON_TO_ARRAY(JSON_KEYS(weighted_items_json));

Refer the below docs and blog page. https://www.singlestore.com/blog/what-is-json-/

https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-to-array/#syntax

SingleStore is more active on their support forum, maybe contact them directly there.

Upvotes: 0

Related Questions