Question

converting json array field into delimited string

  • 10 August 2022
  • 4 replies
  • 261 views

Is there way in SDC to convert jason array/list field into delimited string.

For example, i want to concatenate dropdown_values into one string “ S1 press, S2 Press, S3 Press” for that tasks 

{
            "id": 570894,
            "site": 34,
            "description": "Description 570894",
            "area": 94,
            "line": 199,
            "machine": 2051,
            "created": "2022-08-08T17:47:31.915",
            "lastupdated": "2022-08-08T17:48:03.541",
            "createdby": "203991",
            "lastupdatedby": "203991",
            "tasks": [
                {
                    "type": "dropdown",
                    "text": "Task Description 1",
                    "na": false,
                    "section": "checklist",
                    "dropdown_values": [
                        {
                            "value": "S1 Press"
                        },
                        {
                            "value": "S2 Press"
                        },
                        {
                            "value": "S3 Press"
                        }
                    ],
                    "task_hash": "abc"
                },
                {
                    "type": "input",
                    "text": "Input Coil Number",
                    "na": false,
                    "section": "checklist",
                    "task_hash": "def"
                }
        }


4 replies

Userlevel 5
Badge +1

@meghraj 

Can you please try to do it in Jython or Javascript processor.

Userlevel 4
Badge

@meghraj there are multiple ways to do what you want. Depends on how easy you want to make it and also if you want to do it with standard processors or code. As @Bikram suggested, you could use any code evaluator to do it or use something like field flattener+Expression evaluator to do it if you know that there are fixed number of values in the array.

Also, your json sample had issues. 

 

I had to change a few things to make it a valid JSON. Here:

 

{
   "id":570894,
   "site":34,
   "description":"Description 570894",
   "area":94,
   "line":199,
   "machine":2051,
   "created":"2022-08-08T17:47:31.915",
   "lastupdated":"2022-08-08T17:48:03.541",
   "createdby":"203991",
   "lastupdatedby":"203991",
   "tasks":[
      {
         "type":"dropdown",
         "text":"Task Description 1",
         "na":false,
         "section":"checklist",
         "dropdown_values":[
            {
               "value":"S1 Press"
            },
            {
               "value":"S2 Press"
            },
            {
               "value":"S3 Press"
            }
         ],
         "task_hash":"abc"
      }
   ],
   "type":"input",
   "text":"Input Coil Number",
   "na":false,
   "section":"checklist",
   "task_hash":"def"
}

 

After adding a Field flattener on /tasks[0]/dropdown_values and then using expression evaluator to concatenate 0_value, 1_value, etc you can get the data as shown in field delim below.

 

 

This will work if we know the number of values are fixed in the list. in our case the values could vary from 0 to 20

 

Userlevel 5
Badge +1

@meghraj 

Can you please try the below code snippet in jython evaluator and check if it works for you or not. Below the output based on the input data.

 

#import numpy as np


# Sample Jython code
my_array=''

for record in sdc.records:
  try:
    
    # Write record to processor output
    for i in range(len((record.value['tasks'][0]['dropdown_values']))):
      
      my_array = my_array + record.value['tasks'][0]['dropdown_values'][i]['value'] + ','
  
    record.value['data'] =  my_array
    
    sdc.output.write(record)

  except Exception as e:
    # Send record to error
    sdc.error.write(record, str(e))

 

 

Reply