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

Best Answer

  • moshegmosheg Vertica Employee Administrator
    Answer ✓

    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)
    

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file