Solved: Re: Parse Json multi nested

Robin-Cr · ‎02-08-2023

Hi!

I am having some trouble with converting my HTTP get connector to a working JSON. The ultimate goal is to store the data in a database.

The raw data looks like:

{

"2023-01-01 00:15+01:00": [

{

"unit": "kWh",

"type": "E",

"rate": "low",

"direction": "consumption",

"value": 30.0

},

{

"unit": "kWh",

"type": "E",

"rate": "normal",

"direction": "consumption",

"value": 0

}

],

"2023-01-01 00:30+01:00": [

{

"unit": "kWh",

"type": "E",

"rate": "low",

"direction": "consumption",

"value": 31.0

},

{

"unit": "kWh",

"type": "E",

"rate": "normal",

"direction": "consumption",

"value": 0

}

],

"2023-01-01 00:45+01:00": [

{

"unit": "kWh",

"type": "E",

"rate": "low",

"direction": "consumption",

"value": 32.0

},

{

"unit": "kWh",

"type": "E",

"rate": "normal",

"direction": "consumption",

"value": 0

}

]

}

I have tried a few things, however i keep getting errors saying the values return a NULL or not in a valid array. In this case each date has his own values. It has 2 x the same values. One for normal rate and one of Low rates.

I am really only looking for the one that is filled. So if the low value is filled insert that in a database, if normal is filled fill that one in the database. However i am already having issues with insert all of it in the database.

Does anyone have a solution to parse the above values in a database?

Thanks in advance.

grantjenkins · ‎02-17-2023

Attempt number 20 😉.

See full flow below. I'll go into each of the actions.

JSON is a Compose that contains your data.

Select uses the output from JSON and uses the following expressions:

//From
split(replace(string(outputs('JSON')), ']', '['), '[')

//Map
item()

Filter array uses the output from Select and uses the following expression.

item()

/The actual filter expression is:
@not(startsWith(item(), '}'))

Initialize variable Items creates an Array variable called items that will eventually contain the items we want.

Apply to each iterates over the items in our Filter array in chunks of 2 (2 items at a time). The first item is the date and the second item is the actual objects.

chunk(body('Filter_array'), 2)

Compose uses the following expression to get the objects in JSON format.

json(concat('{"values":[', last(item()), ']}'))

Filter array Non Zero filters our data so only the items where value is greater than zero are kept.

//From
outputs('Compose')?['values']

//Filter
item()?['value']

//The full filter expression is:
@greater(item()?['value'], 0)

Append to array variable Items adds the object to the array while also adding the date property. Because the dates are ISO dates, we can safely assume the length will always be the same regardless of date and time.

addProperty(first(body('Filter_array_Non_Zero')), 'date', slice(first(item()), 2, 24))

That should give you all the objects within the Items array variable. After the Apply to each, I've added another Compose (Output) that just shows the contents of the items array.

And the final output:

[
  {
    "unit": "kWh",
    "type": "E",
    "rate": "normal",
    "direction": "consumption",
    "value": 30,
    "date": "2023-01-01 00:15+01:00"
  },
  {
    "unit": "kWh",
    "type": "E",
    "rate": "low",
    "direction": "consumption",
    "value": 31,
    "date": "2023-01-01 00:30+01:00"
  },
  {
    "unit": "kWh",
    "type": "E",
    "rate": "low",
    "direction": "consumption",
    "value": 32,
    "date": "2023-01-01 00:45+01:00"
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

View solution in original post

grantjenkins · ‎02-08-2023

What would you be looking to add to your database? Did you want the date and the value, or are you just adding all the values, or all properties within the value that has a value?

As an example, which of these are you looking to get out of your JSON data?

[
    30.0,
    31.0,
    32.0
]

Or this:

[
    {
        "date": "2023-01-01 00:15+01:00",
        "value": 30.0
    },
    {
        "date": "2023-01-01 00:30+01:00",
        "value": 31.0
    },
    {
        "date": "2023-01-01 00:45+01:00",
        "value": 32.0
    }
]

Or this:

[
    {
        "date": "2023-01-01 00:15+01:00",
        "unit": "kWh",
        "type": "E",
        "rate": "low",
        "direction": "consumption",
        "value": 30.0
    },
    {
        "date": "2023-01-01 00:30+01:00",
        "unit": "kWh",
        "type": "E",
        "rate": "low",
        "direction": "consumption",
        "value": 31.0
    },
    {
        "date": "2023-01-01 00:45+01:00",
        "unit": "kWh",
        "type": "E",
        "rate": "low",
        "direction": "consumption",
        "value": 32.0
    }
]

Or something else?

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-08-2023

Hi thanks for taking time responding.

Looking to add a mix between the 2nd and last, with the exepction that if the value of normal rate is filled i want to add those values. The data always only has a normal or low rate per 15 min. For example:

[
    {
        "date": "2023-01-01 00:15+01:00",
        "rate": "low",
        "value": 30.0
    },
    {
        "date": "2023-01-01 00:30+01:00",
        "rate": "normal",
        "value": 31.0
    },
    {
        "date": "2023-01-01 00:45+01:00",
        "rate": "low",
        "value": 32.0
    }
]

If it is an impossible task to switch between the two rates i would also be happy with:

[
    {
        "date": "2023-01-01 00:15+01:00",
        "rate": "low",
        "value": 30.0
    },
    {
        "date": "2023-01-01 00:15+01:00",
        "rate": "normal",
        "value": 0
    },
    {
        "date": "2023-01-01 00:30+01:00",
        "rate": "low",
        "value": 0
    },
    {
        "date": "2023-01-01 00:30+01:00",
        "rate": "normal",
        "value": 31.0
    },
    {
        "date": "2023-01-01 00:45+01:00",
        "rate": "low",
        "value": 32.0
    },
    {
        "date": "2023-01-01 00:45+01:00",
        "rate": "normal",
        "value": 0
    }
]

However it would be an added bonus to switch between the two, considering that halves our data. Thanks!

grantjenkins · ‎02-09-2023

Hopefully this is what you're looking for. It was much more challenging than I first thought. I was planning on taking the data, converting to XML, then running some XPath expressions. However, the data (dates) isn't in a valid format for converting to XML, so had to make some changes to make it valid, then reverse at the end. You'll need to do some testing to ensure it works with your data, but hopefully works as expected.

See full flow below. I'll go into each of the actions.

Initialize variable creates a variable of type string called data. This contains the data that you would get from your HTTP connector.

Select splits the data on new line character as input and applies some changes to the date property names so it's valid for converting to XML. The expressions used are.

//From
//You would pass in your data from your HTTP connector instead of the variable that I have
split(variables('data'), decodeUriComponent('%0A'))

//Map
//We're replacing + with --- and : with xxx (except for the last :
if(endsWith(item(), ': ['),
    replace(replace(replace(item(), '+', '---'), ':', 'xxx'), 'xxx ', ':'),
    item()
)

XML is a Compose that converts the data from our Select into XML. The expression used is:

//It joins the array items back to a string, then converts to XML
xml(json(concat('{"root": { value:', json(join(body('Select'), decodeUriComponent('%0A'))), '}}')))

Select Output uses XPath on the XML output to extract out only the items where the value is greater than zero. See expressions below:

//From
xpath(outputs('XML'), '//root/value/*[value/text() > "0"]')

//Date
//We revert our original changes to the date
replace(replace(replace(replace(xpath(item(), 'name(//*)'), '---', '+'), 'xxx', ':'), '_x0020_', ' '), '_x0032_', '2')

//Rate
xpath(item(), 'string(//rate/text())')

//Value
xpath(item(), 'number(//value/text())')

The final output after running the flow is below:

[
  {
    "Date": "2023-01-01 00:15+01:00",
    "Rate": "low",
    "Value": 30
  },
  {
    "Date": "2023-01-01 00:30+01:00",
    "Rate": "low",
    "Value": 31
  },
  {
    "Date": "2023-01-01 00:45+01:00",
    "Rate": "low",
    "Value": 32
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-09-2023

Thanks! The last bit works great. When i have a static varriable the data is succesfully loaded in a database. However i am having some issues when using the HTTP connector instead.

I recon it has to do with the \`s in the post. But when trying to replace them i get different erorrs. You have any idea how solve this issue?

See attached text document. (couldnt upload txt so i zipped it)

Thanks again.

grantjenkins · ‎02-09-2023

Are you able to wrap your HTTP output into a json expression to see if that fixes it?

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-09-2023

That is something i have tried. The first bit of the result i input into the split will then look like:

{"body":{"2023-01-01 00:15+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":30.0},{"unit":"kWh","type":"E","rate":"normal","direction":"consumption","value":0}],"2023-01-01 00:30+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":31.0},{"unit":"kWh","type":"E","rate":"normal","direction":"consumption","value":0}],"2023-01-01 00:45+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":32.0},{"unit":"kWh","type":"E","rate":"normal","direction":"consumption","value":0}],"2023-01-01 01:00+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":31.0},{"unit":"kWh","type":"E","rate":"normal","direction":"consumption","value":0}],"2023-01-01 01:15+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":29.0},{"unit":"kWh","type":"E","rate":"normal","direction":"consumption","value":0}],"2023-01-01 01:30+01:00":[{"unit":"kWh","type":"E","rate":"low","direction":"consumption","value":31.0},

This does look alot like the required conversion. However the split then gives me the following error:

Unable to process template language expressions in action 'Select' inputs at line '0' and column '0': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#split for usage details.'.

Thats why i tried putting into a varriable string. However this then generates the \.

grantjenkins · ‎02-09-2023

That data looks much better. All you would need to do is wrap the inner part of the split into a string expression, so it converts your JSON object to a string.

split(string(YOUR_DATA), decodeUriComponent('%0A'))

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-09-2023

It looks like this now:

The split:

split(string(body('Parse_HTTP')), decodeUriComponent('%0A'))

I get the following error:

grantjenkins · ‎02-09-2023

I'm having a bit of trouble splitting this JSON data ☹️. I'm just off to sleep now (midnight for me), but will have another look tomorrow. Might need to go with a different approach.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

grantjenkins · ‎02-09-2023

Are you able to show some of the output from your Select to see if it's split the data correctly. Just a quick screenshot of the Select output would be fine.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-09-2023

Output of the Parse:

Output of the select:

Robin-Cr · ‎02-14-2023

@grantjenkins Have you had time to look at this issue? It still is something i am having issues with.

grantjenkins · ‎02-15-2023

@Robin-Cr I think I’ve got it working now. Had to go with a slightly different approach. Just off to bed now (midnight for me) but will finish it off and post to you when I get up.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

grantjenkins · ‎02-15-2023

Hopefully this will work for you 🙂

See full flow below. I'll go into each of the actions.

JSON is a Compose that contains your data.

Select Stage 1 uses the following expressions to extract out the data into an array. This is the first stage of transforming the data.

//From
split(replace(string(outputs('JSON')), ']', '['), '[')

//Map
item()

Filter array uses the output from Select Stage 1 and filters out any items that don't contain the word 'unit'. The expression used here is:

item()

Select Stage 2 uses the output from Filter array and transforms the items into proper JSON, but into nested arrays which we will sort out in the next actions. The expression used is:

//Map
json(concat('{"values":[', item(), ']}'))

XML is a Compose that converts the output from Select Stage 2 to XML. The expression used is:

xml(json(concat('{"root": { items:', body('Select_Stage_2'), '}}')))

Select Final uses the output from XML, using some XPath to extract out only the items where the value is greater than 0.

//From
xpath(outputs('XML'), '//root/items/values[value > 0]')

//Map
json(item())?['values']

After running the flow now, we should get the following output.

[
  {
    "unit": "kWh",
    "type": "E",
    "rate": "normal",
    "direction": "consumption",
    "value": "30"
  },
  {
    "unit": "kWh",
    "type": "E",
    "rate": "low",
    "direction": "consumption",
    "value": "31"
  },
  {
    "unit": "kWh",
    "type": "E",
    "rate": "low",
    "direction": "consumption",
    "value": "32"
  }
]

You can then use the output from Select Final to hopefully get what you're after.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-16-2023

Thanks for your response, this indeed seems to work. However i am missing the date field. Any way to get this field in the final stage?

grantjenkins · ‎02-16-2023

Ahhhhh I completely forgot about the date 😮

I'll see what I can do.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

grantjenkins · ‎02-16-2023

Will there always be exactly two items under each date (normal and low)?

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Robin-Cr · ‎02-16-2023

Yes there is.

grantjenkins · ‎02-16-2023

Great - should be able to get the date fairly easily then. Give me 24 hours - just off to sleep then crazy busy day at work tomorrow.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.