Parse Json multi nested

[Robin-Cr]

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 · Accepted solution]

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"
  }
]

[Robin-Cr]

@grantjenkins Thank you so much, that worked! Thanks for your time.