We help IT Professionals succeed at work.

JSON path add parent object

I am selecting nodes in a JSON input but cannot seem to find a way to include the parent object 'Number' for each entry that I am querying. I am using pentaho kettle to query the data using JSON input.

Here are the two fields/paths that display the data;
$..Detail..Quantity
$..Detail..Price

and here is the JSON source
{
      "result": [
            {
                  "Number": "123456",
                  "Detail": [
                        {
                              "Quantity": 1,
                              "Price": 287,
                        },
                        {
                              "Quantity": 1,
                              "Price": 413,
                        }
                  ]
            }
      ]
}

I currently get
1, 287
1, 413

but I need to get
123456, 1, 278
123456, 1, 413
as my JSON source will have multiple 'Number's so will need the 'Number' that each 'Quantity' and 'Price' matches
Comment
Watch Question

leakim971Multitechnician
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
result.Number is 123456
result.Detail[0].Quantity is 1
result.Detail[0].Price return 287
result.Detail[1].Quantity is 1
result.Detail[1].Price return 413

Author

Commented:
result.number does not return anything, I get the error 'We can not find any data with path [result.number]!'

I will not be able to define the Quantity and Price nodes as [1] and [0] as there could be hundreds of nodes and I have not idea how many will exist for each number
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
try:
<script type="text/javascript">

var data={
      "result": [
            {
                  "Number": "123456",
                  "Detail": [
                        {
                              "Quantity": 1,
                              "Price": 287
                        },
                        {
                              "Quantity": 2,
                              "Price": 413
                        }
                  ]
            },
            {
                  "Number": "123456",
                  "Detail": [
                        {
                              "Quantity": 3,
                              "Price": 289
                        },
                        {
                              "Quantity":4,
                              "Price": 415
                        }
                  ]
            }

      ]
};
for( var i=0,resultLimit=data.result.length; i < resultLimit; ++i)
{
	for( j=0,detailLimit=data.result[i].Detail.length; j<detailLimit; ++j)
	{
		alert( data.result[i].Number + " " +data.result[i].Detail[j].Quantity + " " + data.result[i].Detail[j].Price);
	}
}
</script>

Open in new window

Author

Commented:
I have tried this code in a html page which gives me the individual nodes, but I am having a few probs getting this to work properly in pentaho.

I changed the alert to a string variable as the alert was not recognised and now the output is ten duplicate records for the last entry '123456 4 415'

 
var data={
      "result": [
            {
                  "Number": "1234567",
                  "Detail": [
                        {
                              "Quantity": 1,
                              "Price": 287
                        },
                        {
                              "Quantity": 2,
                              "Price": 413
                        }
                  ]
            },
            {
                  "Number": "123456",
                  "Detail": [
                        {
                              "Quantity": 3,
                              "Price": 289
                        },
                        {
                              "Quantity":4,
                              "Price": 415
                        }
                  ]
            }

      ]
};
var stralert = "";
for( var i=0,resultLimit=data.result.length; i < resultLimit; ++i)
{
	for( j=0,detailLimit=data.result[i].Detail.length; j<detailLimit; ++j)
	{
			stralert = ( data.result[i].Number + " " +data.result[i].Detail[j].Quantity + " " + data.result[i].Detail[j].Price);
	}
}

Open in new window

CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
>>I am having a few probs getting this to work properly in pentaho.
Don't know what that is or does.  On another note, line 37 should do concatenation.  Try the attached code:
var stralert = "";
var i=0;
var resultLimit=data.result.length

while( i<resultLimit)
{
	var j=0;
	var detailLimit=data.result[i].Detail.length;
	while(j<detailLimit)
	{
		stralert = stralert + ( data.result[i].Number + " " +data.result[i].Detail[j].Quantity + " " + data.result[i].Detail[j].Price) +"\n";
		j=j+1;
	}
	i=i+1;
}

Open in new window

The solutions offered work as standalone Javascript but cannot get them to work in Pentaho which is the application being used to manage this import function so unable to use the offered solutions

Author

Commented:
The offered solutions did not work in Pentaho which is where the problem was occuring so unable to accept offered solutions