Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Announcements
Celebrating as our community reaches 100,000 members! Thank you!
Solved Go to solution

JSON object must always be treated as an array

Labels:
avatar
author-rank MarioFRS
Contributor

Created ‎10-12-2022 04:14 AM

Hello everyone.

i need help. I have a JSON file which contains in an object either an array of other objects or a single object. This is the relevant object "Product": [ or "Product": { .

How can I configure with the JOLT Transform processor to always expect an array in the object? The product object must always be treated as an array, even if it contains only a single object.

 

{
  "costc": 9638,
  "sum_amount": 543,
  "sum_invitation": 0,
  "sum_selfconsumption": 0,
  "sum_loss": 0,
  "sum_gross": "2764,000",
  "sum_net": "2392,480",
  "sum_margin": "2003,780",
  "sum_costs_total": "388,700",
  "sum_costs": "388,700",
  "sum_costs_invitation": 0,
  "sum_costs_selfconsumption": 0,
  "sum_costs_loss": 0,
  "sum_costs_total_percent": "16,25",
  "sum_costs_percent": "16,25",
  "Classes": {
    "ClassSummary": [
      {
        "class": 1102,
        "sum_amount": 117,
        "sum_invitation": 0,
        "sum_selfconsumption": 0,
        "sum_loss": 0,
        "sum_gross": "740,500",
        "sum_net": "692,060",
        "sum_margin": "552,385",
        "sum_costs_total": "139,675",
        "sum_costs": "139,675",
        "sum_costs_invitation": 0,
        "sum_costs_selfconsumption": 0,
        "sum_costs_loss": 0,
        "sum_costs_total_percent": "20,18",
        "sum_costs_percent": "20,18",
        "ProductDetails": {
          "Product": [
            {
              "id": 7992160,
              "artnr": 32212,
              "sum_amount": 16,
              "sum_invitation": 0,
              "sum_selfconsumption": 0,
              "sum_loss": 0,
              "sum_gross": "80,000",
              "sum_net": "74,770",
              "sum_margin": "53,243",
              "sum_costs_total": "21,527",
              "sum_costs": "21,527",
              "sum_costs_invitation": 0,
              "sum_costs_selfconsumption": 0,
              "sum_costs_loss": 0,
              "sum_costs_total_percent": "28,79",
              "sum_costs_percent": "28,79",
              "value": null
            },
            {
              "id": 7794909,
              "artnr": 32205,
              "sum_amount": 7,
              "sum_invitation": 0,
              "sum_selfconsumption": 0,
              "sum_loss": 0,
              "sum_gross": "56,000",
              "sum_net": "52,340",
              "sum_margin": "39,473",
              "sum_costs_total": "12,867",
              "sum_costs": "12,867",
              "sum_costs_invitation": 0,
              "sum_costs_selfconsumption": 0,
              "sum_costs_loss": 0,
              "sum_costs_total_percent": "24,58",
              "sum_costs_percent": "24,58",
              "value": null
            },
            {
              "class": 19174,
              "sum_amount": 17,
              "sum_invitation": 0,
              "sum_selfconsumption": 0,
              "sum_loss": 0,
              "sum_gross": "85,000",
              "sum_net": "71,430",
              "sum_margin": "71,430",
              "sum_costs_total": 0,
              "sum_costs": 0,
              "sum_costs_invitation": 0,
              "sum_costs_selfconsumption": 0,
              "sum_costs_loss": 0,
              "sum_costs_total_percent": 0,
              "sum_costs_percent": 0,
              "ProductDetails": {
                "Product": {
                  "id": 7925180,
                  "artnr": 6201,
                  "sum_amount": 17,
                  "sum_invitation": 0,
                  "sum_selfconsumption": 0,
                  "sum_loss": 0,
                  "sum_gross": "85,000",
                  "sum_net": "71,430",
                  "sum_margin": "71,430",
                  "sum_costs_total": 0,
                  "sum_costs": 0,
                  "sum_costs_invitation": 0,
                  "sum_costs_selfconsumption": 0,
                  "sum_costs_loss": 0,
                  "sum_costs_total_percent": 0,
                  "sum_costs_percent": 0,
                  "value": null
                }
              }
            }
          ]
        }
      }
    ]
  },
  "date": "2022-10-06"
}

 

 

2,886 Views
0 Kudos
1 ACCEPTED SOLUTION

avatar
author-rank ChuckE
Expert Contributor

Created on ‎10-17-2022 11:48 AM - edited ‎10-17-2022 11:57 AM

@MarioFRS Ah, you are correct.  I overlooked that additional nested Product.  That complicates things because the upper Product can be an array or not any array, which itself contains another ProductDetails.Product which also may or may not be an array.  Ugh!

To solve this I had to resort to chaining multiple transforms to account for all the possible formats that can exist. I may not have all the possible permutations you'll have, but you can follow the basic structure of these transforms and create new ones with additional levels if necessary.

It is important to note that the inner-most portions of the nested data need transformed first, so the order of the transforms starts with the inner-most data and ends with the outer-most data.  Keep that in mind if you need to add more transforms to account for additional permutations of the data.

 

Give this a try and let me know how it works for you.

 

 

[
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "*": {
                "*": {
                  "*": {
                    "Product": "MANY"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "*": {
                "*": {
                  "Product": "MANY"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "Product": "MANY"
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": {
            "Product": "MANY"
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "ClassSummary": "MANY"
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "ClassSummary": "MANY"
      }
    }
  }
]

 

 

View solution in original post

2,655 Views
0 Kudos
13 REPLIES 13

avatar
author-rank ChuckE
Expert Contributor

Created ‎10-12-2022 03:37 PM

Try this...

 

[
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
      		"Product": "MANY"
      	}
      }
    }
  }
]

 

2,500 Views

avatar
author-rank MarioFRS
Contributor

Created ‎10-12-2022 11:26 PM

@ChuckEIt does not work. I still get the same output as the input is. The single object is not yet treated as an array. Do you have another idea

2,478 Views
0 Kudos

avatar
author-rank ChuckE
Expert Contributor

Created ‎10-13-2022 01:40 AM

"Product" is nested several layers deep so the first thing you need to do is match the wildcard (*) character to match the depth of where "Product" is located.  As a simplified example, I trimmed and flattened your JSON data down a little, and the transform works as expected.  Where I ran into a challenge was with the "ClassSummary" array, because I don't know how to account for it in the structure.  Hopefully this gives you something to work with, and using this web site you get some ideas on how to take it further.  I'm very curious to see the result.

 

https://jolt-demo.appspot.com/#ritwickgupta

 

[
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "Product": "MANY"
        }
      }
    }
  }
]
{
  "costc": 9638,
  "sum_amount": 543,
  "Classes": {
    "class": 1102,
    "sum_amount": 117,
    "sum_invitation": 0,
    "ProductDetails": {
      "Product": {
        "id": 7992160,
        "artnr": 32212,
        "sum_amount": 16,
        "sum_invitation": 0,
        "value": null
      }
    }
  },
  "date": "2022-10-06"
}

 

2,461 Views
0 Kudos

avatar
author-rank MarioFRS
Contributor

Created ‎10-13-2022 04:57 AM

@ChuckEThanks for your explanation. I had also tested it with a simplified json file and it worked there. I have replaced the expected wildcards with the names of the objects to clarify the structure. And I still have no idea why it does not work

[
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "ClassSummary": {
          "ProductDetails": {
            "Product": "MANY"
          }
        }
      }
    }
  }
]

 

2,447 Views
0 Kudos

avatar
author-rank ChuckE
Expert Contributor

Created ‎10-13-2022 08:33 AM

This one works.  Give it a try...

[
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "ClassSummary": "MANY",
          "*": {
            "*": {
              "Product": "MANY"
            }
          }
        }
      }
    }
  }
]
2,430 Views
0 Kudos

avatar
author-rank MarioFRS
Contributor

Created ‎10-13-2022 11:49 AM

This works not with a combination of single object and an array of objects for "product". It works for me only with a single object. For example

{
  "costc": 9638,
  "sum_amount": 543,
  "sum_invitation": 0,
  "sum_selfconsumption": 0,
  "sum_loss": 0,
  "sum_gross": "2764,000",
  "sum_net": "2392,480",
  "sum_margin": "2003,780",
  "sum_costs_total": "388,700",
  "sum_costs": "388,700",
  "sum_costs_invitation": 0,
  "sum_costs_selfconsumption": 0,
  "sum_costs_loss": 0,
  "sum_costs_total_percent": "16,25",
  "sum_costs_percent": "16,25",
  "Classes": {
    "ClassSummary": [
      {
        "class": 1102,
        "sum_amount": 117,
        "sum_invitation": 0,
        "sum_selfconsumption": 0,
        "sum_loss": 0,
        "sum_gross": "740,500",
        "sum_net": "692,060",
        "sum_margin": "552,385",
        "sum_costs_total": "139,675",
        "sum_costs": "139,675",
        "sum_costs_invitation": 0,
        "sum_costs_selfconsumption": 0,
        "sum_costs_loss": 0,
        "sum_costs_total_percent": "20,18",
        "sum_costs_percent": "20,18",
        "ProductDetails": {
          "Product": {
            "id": 7992160,
            "artnr": 32212,
            "sum_amount": 16,
            "sum_invitation": 0,
            "sum_selfconsumption": 0,
            "sum_loss": 0,
            "sum_gross": "80,000",
            "sum_net": "74,770",
            "sum_margin": "53,243",
            "sum_costs_total": "21,527",
            "sum_costs": "21,527",
            "sum_costs_invitation": 0,
            "sum_costs_selfconsumption": 0,
            "sum_costs_loss": 0,
            "sum_costs_total_percent": "28,79",
            "sum_costs_percent": "28,79",
            "value": null
          }
        }
      }
    ]
  },
  "date": "2022-10-06"
}

 

2,413 Views
0 Kudos

avatar
author-rank ChuckE
Expert Contributor

Created ‎10-13-2022 12:26 PM

Hmm.  I tried all the use cases (single object, array, no array) and this transform works with all the example data that you've posted.  Can you send a screen shot of what you mean; perhaps I don't understand the problem.

2,408 Views
0 Kudos

avatar
author-rank MarioFRS
Contributor

Created ‎10-13-2022 12:54 PM

I will try it. As you can see in the example, it can happen that a class within the classes summary, object product details can have an array of objects, or a single object.

MarioFRS_1-1665690783363.pngMarioFRS_2-1665690793376.png

As you can see in the example, it can happen that a class within the classes Summary, Object Product Details can have an array of objects or a single object.

And the Jolt Spec has no influence that a single object becomes an array.

2,381 Views
0 Kudos

avatar
author-rank ChuckE
Expert Contributor

Created ‎10-13-2022 02:16 PM

Based on this screen shot it appears you are using the wrong Jolt Transform.  Try using the newest version that I sent earlier this morning.  

I've also attached screen shots which show the Jolt works with your data.  I trimmed your data up a little for brevity purposes, but the structure is still the same.

 

Screen Shot 2022-10-13 at 16.10.37.pngScreen Shot 2022-10-13 at 16.06.43.png

2,365 Views
0 Kudos
Announcements
Product Announcements
[ANNOUNCE] Cloudera on Public Cloud Runtime 7.2.18 is GA!
What's New @ Cloudera
Cloudera Operational Database (COD) supports enhancements to...
Community Announcements
March 2024 Community Highlights
Community Announcements
Celebrating 100,000 Members: A Journey of Growth and Connect...
Product Announcements
[ANNOUNCE] New Cloudera JDBC Connector for Apache Hive 2.6.2...
View More Announcements