Query JSON by specific property (for inserting into SQL Server, or other)

(0) Share

Report

Posted on by MarcDussault

Hi all,

I have a long JSON coming from an API call and I need to store the values in a SQL table.

My issue is that I need to be able to direct the <Insert row (V2)> to select the correct item from the array to put into the right SQL column. I can do it by referencing it with the [0],[1] etc, but as soon as the structure of the initial API data changes (which it will) the Insert Row will no longer properly work.

What I would like is to be able to dynamically filter using the ID (or label) into the relevant fields for the Insert Row action.

I would like to avoid needing to create a <filter> action since I have potentially several hundred of them to create.

Example with SQL syntax:

{
 "body": [
 {
 "ID": "163716684587818",
 "label": "Sexe",
 "value": "Féminin"
 },
 {
 "ID": "163716691587418",
 "label": "Date de naissance",
 "value": "01/05/2000"
 },
 {
 "ID": "163716696241718",
 "label": "Ville de naissance",
 "value": "Paris"
 }
 ]
}

Categories:

Building flows

I have the same question (0)

All responses (1)

Answers (0)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report
@MarcDussault

You can create variables to store the values you want to insert into the SQL table. For example, create variables named "ID", "label", and "value". These variables will hold the extracted values from the JSON data using XPath expressions.

For example, if you want to extract the "label" property from the JSON array, you can use the XPath expression
xpath(body('Parse_JSON'), 'string(/body/label)')
.

Was this reply helpful? Yes No