Extracting specific JSON locale key value pair into columns

Hello,
I am exploring getting JSON locale specific key value into columns . I tried using Cell splitter which is able to create multiple columns , but I want the column to be named as columnname+locale and only have value as the column value of the locale in it . The JSON string for ref is as below which is for Name attribute
{ “bg-BG”: “Sample Ii Jr”, “cs”: “Sample II Jr”, “da”: “”, “nl”: “”, “en”: “Sample II JR”, “en-US”: “”, “fr-CH”: “”, “de-AT”: “Reece II Sandalen”, “de-CH”: “”, “el”: “Sample Ii Jr”, “hu-HU”: "Sample II Jr. ", “nb”: “”, “es”: “Sample Sample II”}

I would like to have Name_bg , Name_da , Name_en, Name_enUS, … as the column names with respective value in the associated columns.

With Cellspliter , I get column with value as of “cs”: “Sample II Jr” and column name as Name(#1) ,…

Pls do help .

Thanks,
Nivedita

Hello @nivedita_dixit

Aiming to extract the columns of your JSON, you just need a couple of nodes:

image
20220629_parse_json.knwf (10.9 KB)

The key is the JSON Query, it will allow you to parse your JSON into Columns. The ‘JSON Path’ configuration node has to be configured as in the picture:

Then, the ‘Ungroup’ node will return you the data in columns shape.

BR

1 Like

Thanks @gonhaddock . I had tried JSON Path and Ungroup, but could not get it to work . the JSON string provided is just one of the part of the JSON collection. Also the JSON string provided is dynamic and other rows in the JSON could have additional locales. Would greatly help if you can suggest on how to approach it

Regards,
Nivedita

Hi @nivedita_dixit
Aiming to test a workflow with the mentioned constrains; as dynamic, additional headers … would be great if you can provide a sample set of JSONS as the one provided before, having these casuistic.

Looks complex. I haven’t experienced with these nodes in this dynamic way before. But some other in the forum can support with experience.

BR

1 Like

Hi @gonhaddock ,

Below is the sample subset of the huge JSON . It would greatly help , please do suggest appropriate approach to have them into columnar data
Thanks,
Nivedita

 {
  "Product ID": "xxxx",
  "Product number": "xxx",
  "Name": "{  \"bg-BG\": \"Sample Ii Jr\",  \"cs\": \"Sample II \",  \"da\": \"\",  \"nl\": \"\",  \"en\": \"Sample II JR\",  \"en-US\": \"\",  \"fr-CH\": \"\",  \"de-AT\": \"Sample YY\"}",
  "Description": "{  \"bg-BG\": \" MC KINLEY\",  \"cs\": \"Testing, sample.\",  \"da\": \"\",  \"nl\": \"\",  \"en\": \"\",  \"en-US\": \"\",  \"fr-CH\": \"\" }",
  "Product short Description": "{  \"bg-BG\": \"  Temp.\",  \"cs\": \"PSample\",  \"da\": \"\",  \"nl\": \"\",  \"en\": \" style\",  \"en-US\": \"\",  \"fr-CH\": \"\",  \"de-AT\": \"Material: Polyurethane, Textil, TRP\"}",
}

Hello @nivedita_dixit

I had a look into it.
Could be this a valid approach to the challenge?
The trick is to use the most complete query for all the columns.
As you see in the ‘Description’ column JSON; you can see seven items and however it ends up with 8 columns…

I’ve used a REGEX column rename giving a nested hierarchy in the column name; just guessing on how the output should be…

20220629_parse_json_v2.knwf (42.2 KB)

BR

1 Like

Hello again @nivedita_dixit

I think your JSON source is generating a string for each of the columns, since some square brackets are missed at each of the hierarchy levels; and all the reverse bars aren’t very standard . Something like this:


“Name”: [ { “bg-BG”: “Sample Ii Jr”, …

Anyway, the workflow passes through it without modifying the source.

BR

1 Like

Hello @gonhaddock ,

Thanks a lot, worked like charm .

Regards,
Nivedita

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.