Forum Discussion

mohit_jain's avatar
mohit_jain
New Contributor III
2 years ago

JSON STRING VALUES INTO COLUMNS

Hi Everyone..

I have a requirement where we have to split the json which is coming into the json but it is in String. We have to divide into the columns.

Let me explain you by the example. 
The Json is coming in this format.

[
{
"String":
"{col_name=CTRY_CODE, data_type=varchar(32768)},{col_name=SRCE_CODE, data_type=varchar(32768)},{col_name=PROD_CODE, data_type=varchar(32768)}}}"
}
]

 

But the result we need is :

Can anyone help me to solve this.

Thanks in advance.

alchemiz j_angelevski 

 

2 Replies

  • Matthias's avatar
    Matthias
    New Contributor III
    2 years ago

    I think your initial problem is that the data is not actual json.

    Field separators should be ":" - and the parenteses are clearly off.

    Snaplogic will still however allow you to do this (you can probably condense this into one mapper if you give it a bit more thought ...):

    first mapper: `$String.split('},{')` 

    2nd mapper: `$splitValue.replace('{', '').replaceAll('}}}', '').split(', ').toObject(x => x.split('=')[0], x => x.split('=')[1])`

     

    The json splitter in between just splits the data into multiple rows.

     

     

     

  • alchemiz's avatar
    alchemiz
    Contributor III
    2 years ago

    Hi mohit_jain,

    Good day, you can also try to manipulate the string to be a valid JSON string.. just like what Sir Matthias suggested

    Expression:
    JSON.parse('['.concat($.get('String','').replace(/\{|\=|\}|\,[^\{]/g, m => match m {'{'=> '{"', '='=> '":"', '}'=> '"}', ', '=> '","', ','=> '","'}),']'))