How to put json with array values into varchar columns
Hi,
I hope you can give me some advice
I have JSON to load which contains several key:value pairs that contain arrays of integer:
{ "actionId":2, "actionWay":"default", "actionWayId":1, "countryCode":"USA", "createdAt":"2023-11-28 11:46:09.678555", "decisionSource":"aws", "platform":"mobSite", "decisionSourceTriggers":[813, 912, 933], "scoredTriggers":[813, 1197], "triggersId":[813, 1197], "triggersIdsFirstTime":[813, 654, 643], "triggersIdsPrevious":[1197] }
Is it possible to put this json into the table as a record which will store every array in the column of varchar datatype:
actionId actionWay actionWayId ... decisionSourceTriggers scoredTriggers triggersIdsFirstTime ... 2 'default' 1 ... '[813, 912, 933]' '[813, 1197]' '[813, 654, 643]' ...
I tried to play with MAPITEMS + LISTAGG functions, but I can't use several MAPITEMS functions in a single query:
SELECT actionId, ... MAPVALUES(triggersIdsFirstTime) OVER(PARTITION BY actionId, ...) triggersIdsFirstTime, MAPVALUES(scoredTriggers) OVER(PARTITION BY actionId, ...) scoredTriggers FROM tmp_arrays_json;
Also, I can use simple MAPTOSTRING function:
SELECT MAPTOSTRING(MapLookUp(__raw__,'triggersIdsFirstTime'))::varchar(2048) FROM tmp_arrays_json;
but a resulting format of string is unacceptable for me:
{ "0": "813", "1": "654", "2": "643" }
Also I considered loading data in ARRAY data type, but it really must be a string and I didn't find a way to convert arrays to strings.
And now I really don't know is there any other solutions.
Thank you
Tagged:
0
Best Answer
-
mosheg Vertica Employee Administrator
Does the following example suit your needs?
CREATE TABLE t001 ( actionId int, actionWay varchar(80), actionWayId int, countryCode varchar(80), createdAt timestamp, decisionSource varchar(80), decisionSourceTriggers array[int8](65000), platform varchar(80), scoredTriggers array[int8](65000), triggersId array[int8](65000), triggersIdsFirstTime array[int8](65000), triggersIdsPrevious array[int8](65000) ); CREATE TABLE t002 ( actionId int, actionWay varchar(80), actionWayId int, countryCode varchar(80), createdAt timestamp, decisionSource varchar(80), decisionSourceTriggers varchar(100), -- array[int8](65000), platform varchar(80), scoredTriggers varchar(100), -- array[int8](65000), triggersId array[int8](65000), triggersIdsFirstTime varchar(100), -- array[int8](65000), triggersIdsPrevious array[int8](65000) ); copy t001 from '/YourPath/YourDataFile.json' PARSER FJsonParser(); \! vsql -XAt -F "#" -c "SELECT actionId, actionWay, actionWayId, decisionSourceTriggers, scoredTriggers, triggersIdsFirstTime FROM t001;" | vsql -c "copy t002(actionId,actionWay, actionWayId,decisionSourceTriggers, scoredTriggers, triggersIdsFirstTime) from stdin delimiter '#' abort on error;" SELECT actionId, actionWay, actionWayId, -- other columns decisionSourceTriggers, scoredTriggers, triggersIdsFirstTime FROM t002; actionId | actionWay | actionWayId | decisionSourceTriggers | scoredTriggers | triggersIdsFirstTime ----------+-----------+-------------+------------------------+----------------+---------------------- 2 | default | 1 | [813,912,933] | [813,1197] | [813,654,643] (1 row)
0
Answers
That's the approach, I think I can work with that option. Thank you for help!