HELP PLEASE - Parsing JSON to flat file

[Anonymous]

Hi,

I have the following json file that I would like to parse out specific fields (in bold)

{ 
 "31265327613" : {
    "siteId" : "US",
    "entityTypeId" : 11111,
    "productid" : 31265327613,
    "quality" : -1.0,
    "productAttrs" : {
      "1" : {
        "attrTypeId" : 1,
        "name" : "color",
        "dataType" : "TEXT",
        "instock" : 1.0,
        "prodValues" : [ {
          "value" : "blue",
          "dimensions" : 2.0,
          "demand" : 1.0
        } ]
      },
      "2" : {
        "attrTypeId" : 2,
        "name" : "band",
        "dataType" : "TEXT",
        "instock" : 1.0,
        "prodValues" : [ {
          "value" : "gsm",
          "dimensions" : 2.0,
          "demand" : 1.0
        } ]
      },
      "3" : {
        "attrTypeId" : 3,
        "name" : "productid",
        "dataType" : "NUMERIC",
        "instock" : 1.0,
        "prodValues" : [ {
          "value" : "31265327613",
          "dimensions" : 2.0,
          "demand" : 3.0
        } ]
      }
    }
  }
}

I want the output to be a tab delimited text file with the following structure

productid        | color | band

31265327613 | blue  | gsm

few notes - the "names" can contain various names and (for the sake of the example I just provided 3.
each product can have different values for "names" (so please don't hard code the values in your reply)

I understand I need to go down the tree and iterate on the elements, extract what I need, exit and move to the next one. tried many ways with tFileInputJson and with tExtractJsonFields, but with no success.
I suspect I'm not using the correct syntax for the loop.

thank you so much in advance, would appriciete if you could include screenshot of how you build the components with especially the tJSON ones.

[vapukov]

Hi,

it possible, but of course not trivial.

first of all need to know more information about file:

• is it API responce for single id (

  { 
   "31265327613" : {

  or it is file with many of them, and you need first extract all id list?
• is it always only 3 attribute - "1", "2" and "3" or many of them? is numbers (tags) always the same or "1" could be and color and weight?  
• is prodValues array always contain only 1 element or many of them

depending on the answers - how will look structure in most worst case? 

[Anonymous]

Thank you @vapukov 

1. No, this is not a single response. I have a file full of the example above, a block for each productid. (for this post, I only uploaded block)
the productid is mentioned both in the root level, as well as one level below as you can see in my example.

2. No, it is not always 3 attributes. for the purpose of the post I shortened the example to only 3.
The numbers tags are merely order sequence, I don't care about them, and don't need to extract them.

3. No. it can have several value. for example color can have several values. see example:

      "1" : {
        "attrTypeId" : 1,
        "name" : "color",
        "dataType" : "TEXT",
        "instock" : 1.0,
        "prodValues" : [ {
          "value" : "black",
          "dimensions" : 2.0,
          "demand" : 1.0
        }, {
          "value" : "beige",
          "dimensions" : 2.0,
          "demand" : 1.0
        }, {
          "value" : "orange",
          "dimensions" : 2.0,
          "demand" : 1.0
        } ]

[David_Beaty]

Hi,

Sounds like you need to extract each attribute and value as a key/value pair and write them into a temporary table and using some kind of pivot, combine them all for a given product ID.

[Anonymous]

Yes,

extracting it as Name-Value Pairs will also help me

Like this:

31265327613 | Color | Blue
31265327613 | Color | Red
31265327613 | Color | Yellow

don't know how to do that

[Anonymous]

Hi,

I create the maximum list of possible productAttrs and use tFileInputJSON. If not, it returns NULL.

For ex. country not exist for current JSON and we have NULL, in other it will. 

The main approach create full list of productAttr, if it's possible.(i think it is possible )

Thanks

Capture_T.PNG