Skip to main content
Question

How to put json with array values into varchar columns

  • December 1, 2023
  • 2 replies
  • 6 views
greyofthedawn

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

    2 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • December 1, 2023

    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)
    greyofthedawn
    • greyofthedawn
    • Author
    • New Participant
    • December 3, 2023

    That's the approach, I think I can work with that option. Thank you for help!

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement