Unanswered

Json Each Column is an Array

(1) Share

Report

Posted on by NickTLS

The Json payload I am using is return each record in a column as an array. How can I split this out so that each complete record is in a single row for an HTML Table?

{
	"accessionNumber": [
		"0001731122-21-001488",
		"0001731122-21-001402",
		"0001731122-21-001368"
	],
	"filingDate": [
		"2021-09-10",
		"2021-08-17",
		"2021-08-16"
	],
	"reportDate": [
		"2021-09-08",
		"2021-08-16",
		"2021-08-12"
	],
	"acceptanceDateTime": [
		"2021-09-10T12:27:43.000Z",
		"2021-08-17T17:10:34.000Z",
		"2021-08-16T16:18:54.000Z"
	],
	"act": [
		"33",
		"34",
		"34"
	],
	"form": [
		"10-Q",
		"8-K",
		"424B3"
	],
	"fileNumber": [
		"333-252569",
		"001-38545",
		"001-38545"
	],
	"filmNumber": [
		"211168858",
		"211168187",
		"211153230"
	],
	"items": [
		"2.02,9.01",
		"2.02,9.01",
		"2.01,7.01,9.01"
	],
	"size": [
		4923,
		8184,
		7394
	],
	"isXBRL": [
		0,
		1,
		1
	],
	"isInlineXBRL": [
		0,
		1,
		1
	],
	"primaryDocument": [
		"e3017_424b3.htm",
		"lhi-20210630.htm",
		"e3006_8-k.htm"
	],
	"primaryDocDescription": [
		"10-Q",	
		"FORM 8-K"
		"FORM 424B3"
	]
}

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @NickTLS

Here I can see each array have three values. Is always three values fixed?

Thanks

Was this reply helpful? Yes No
NickTLS 54 on at

Like (1)

Report

No, it can have N number of values… it’s value is part of a row. So if one field is blank, say “form”, it will have a “” as the entry.

So for each column[0] is row 1, column[1] is row 2….. filenumber in essence would be the primary key in the DB.

Odd that it’s formatted this way.

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @NickTLS

Before I can suggest some possible solution I have one more question please. Yes you mentioned it can have N number of values. So that means some array can have 6 values. That means all the accessionnumber, filingdate, report date all have 6 values each. Isn't?

Was this reply helpful? Yes No
NickTLS 54 on at

Like (0)

Report

All will have the same number of records in the array

Was this reply helpful? Yes No
Verified answer

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @NickTLS

Here I am not using all the JSON elements from your sample data. So extend it accordingly.

Please follow the below steps.

Above I am using only two two JSON elements Accession Number & Filing Date. Declare other variables as an array and extend it accordingly.

Next I have used a compose action step and copied your JSON sample data.

Next use a Parse JSON to parse the above JSON data.

Above JSON Schema is as follows:

{

    "type": "object",

    "properties": {

        "accessionNumber": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "filingDate": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "reportDate": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "acceptanceDateTime": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "act": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "form": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "fileNumber": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "filmNumber": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "items": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "size": {

            "type": "array",

            "items": {

                "type": "integer"

            }

        },

        "isXBRL": {

            "type": "array",

            "items": {

                "type": "integer"

            }

        },

        "isInlineXBRL": {

            "type": "array",

            "items": {

                "type": "integer"

            }

        },

        "primaryDocument": {

            "type": "array",

            "items": {

                "type": "string"

            }

        },

        "primaryDocDescription": {

            "type": "array",

            "items": {

                "type": "string"

            }

        }

    }

}

Next set the JSON each elements under the array variables.

Again above extend the flow to set other variable arrays accordingly.

Next initialise two variables. See below.

Next we need to use a Do until loop action step and extract each values from the above array.

Above expressions I used are as follows:

First expression I used is under the Do Until loop

sub(length(variables('AccessionNumber')),1)

Next expression is under the compose action step

variables('AccessionNumber')[variables('ArrayCounter')]

variables('FilingDate')[variables('ArrayCounter')]

Extend the compose action step variable name and expressions accordingly to accommodate other JSON elements.

Finally outside the Do until loop use Create HTML Table action step.

Once you construct the above your final flow will look like this.

Here is my unit test results:

As you can see above flow has created an HTML table with each row contains values from your JSON array elements.

Hope it is useful and if you have any questions please let me know.

Thanks

Was this reply helpful? Yes No
NickTLS 54 on at

Like (1)

Report

Makes total sense! I will test it tonight but I don’t see why this wouldn’t work. I was thinking there was some special function already available to do this sort of conversion but your way makes a lot of sense and will be easier to debug if I do run into issues.

Thanks for taking the blinders off!

Was this reply helpful? Yes No