Answered

How do I get data from a JSON Scheme that are located in cells?

(0) Share

Report

Posted on by therush321

Hi All

I am calling an API whereby the JSON Scheme has the data located in the cells part under the field textValue which is related to my 3 headers and need help deconstructing it to get the text value out so I can save it to a database.

Thank you in advance. 🙏

JSON Scheme below:

{
 "type": "object",
 "properties": {
 "body": {
 "type": "object",
 "properties": {
 "d": {
 "type": "object",
 "properties": {
 "header": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "displayName": {
 "type": "string"
 },
 "uri": {
 "type": "string"
 }
 },
 "required": [
 "displayName",
 "uri"
 ]
 }
 },
 "rows": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "cells": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "dataType": {
 "type": "string"
 },
 "textValue": {
 "type": "string"
 },
 "objectType": {
 "type": "string"
 },
 "uri": {
 "type": "string"
 },
 "dateValue": {
 "type": "object",
 "properties": {
 "day": {
 "type": "integer"
 },
 "month": {
 "type": "integer"
 },
 "year": {
 "type": "integer"
 }
 }
 }
 },
 "required": [
 "dataType",
 "textValue"
 ]
 }
 }
 },
 "required": [
 "cells"
 ]
 }
 }
 }
 }
 }
 }
 }
}

HTTP output sample data:

{
 "statusCode": 200,
 "headers": {
 "Transfer-Encoding": "chunked",
 "Connection": "keep-alive",
 "x-execution-correlation-id": "ab331ac7647fc231df13b",
 "x-frame-options": "SAMEORIGIN",
 "strict-transport-security": "max-age=31536000",
 "content-security-policy": "frame-ancestors 'self' https://psa-eu1.Gizmo.com https://ti.Gizmo.com https://zt.Gizmo.com;",
 "vary": "Accept-Encoding",
 "CF-Cache-Status": "DYNAMIC",
 "CF-RAY": "80eb9008f-AMS",
 "Date": "Sat, 30 Sep 2023 09:53:52 GMT",
 "Server": "cloudflare",
 "Content-Type": "application/json; charset=utf-8",
 "Content-Length": "8363"
 },
 "body": {
 "d": {
 "header": [
 {
 "displayName": "Holiday Name",
 "uri": "urn:Gizmo:holiday-list-column:holiday-name"
 },
 {
 "displayName": "Holiday Calendar",
 "uri": "urn:Gizmo:holiday-list-column:holiday-calendar"
 },
 {
 "displayName": "Holiday Date",
 "uri": "urn:Gizmo:holiday-list-column:date"
 }
 ],
 "rows": [
 {
 "cells": [
 {
 "dataType": "urn:Gizmo:list-type:string",
 "textValue": "Africa Day"
 },
 {
 "dataType": "urn:Gizmo:list-type:object",
 "objectType": "urn:Gizmo:object-type:holiday-calendar",
 "textValue": "Zambia",
 "uri": "urn:Gizmo-tenant:1af2670c0:holiday-calendar:44"
 },
 {
 "dataType": "urn:Gizmo:list-type:date",
 "dateValue": {
 "day": 25,
 "month": 5,
 "year": 2023
 },
 "textValue": "2023/05/25"
 }
 ]
 },
 {
 "cells": [
 {
 "dataType": "urn:Gizmo:list-type:string",
 "textValue": "All Saints' Day"
 },
 {
 "dataType": "urn:Gizmo:list-type:object",
 "objectType": "urn:Gizmo:object-type:holiday-calendar",
 "textValue": "France",
 "uri": "urn:Gizmo-tenant:2a3c370c0:holiday-calendar:4"
 },
 {
 "dataType": "urn:Gizmo:list-type:date",
 "dateValue": {
 "day": 1,
 "month": 11,
 "year": 2018
 },
 "textValue": "2018/11/01"
 }
 ]
 },
 {
 "cells": [
 {
 "dataType": "urn:Gizmo:list-type:string",
 "textValue": "All Saints' Day"
 },
 {
 "dataType": "urn:Gizmo:list-type:object",
 "objectType": "urn:Gizmo:object-type:holiday-calendar",
 "textValue": "France",
 "uri": "urn:Gizmo-tenant:62e11e2a3c370c0:holiday-calendar:4"
 },
 {
 "dataType": "urn:Gizmo:list-type:date",
 "dateValue": {
 "day": 1,
 "month": 11,
 "year": 2019
 },
 "textValue": "2019/11/01"
 }
 ]
 }

I have the same question (0)

All responses (11)

Answers (1)

lbendlin 8,332 Super User 2025 Season 2 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

You'll have to construct the traversal yourself

[body][properties][d][properties][items][properties][cells][items][properties][textValue]

(potentially without the [properties] pieces)

Was this reply helpful? Yes No
therush321 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

@lbendlin , how would one achieve that? Sorry, for I am not so clued up on that.

Was this reply helpful? Yes No
lbendlin 8,332 Super User 2025 Season 2 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

So to get to the text values you probably would want to enumerate through the rows

[body][d][rows]

and then for each row get the [cells], iterate through these, and get the [textValue].

What is the format that you need to use to feed your database?

Was this reply helpful? Yes No
therush321 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

@lbendlinThanks. I managed to get the data enumerated but still need to map it out.
All of them are text except the date value which is a date format in my SQL tabel.

Was this reply helpful? Yes No
Matthy79 4,178 Super User 2024 Season 1 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

Hello @therush321,

This seems too complicated. Are there always different headers or positions of the headers? If yes, how do you manage to put them in matching fields in your database? Because if for some reason the API, or wherever you are getting the data from, adds new columns, you won't have that new column automatically added in your database.

If the fields don't change, I would suggest to simply use one select statement to get the values you are searching for. This could look like this:

From is your rows-array and you can use this expressions to get the values for the different columns.

item()['cells'][0]['textValue']

item()['cells'][1]['textValue']

item()['cells'][2]['textValue']

Was this reply helpful? Yes No
therush321 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

@Matthy79 , thanks but I am getting an error:

Was this reply helpful? Yes No
Verified answer

Matthy79 4,178 Super User 2024 Season 1 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

Hello @therush321,

You don’t need any apply to each. Just the select action I posted.

Was this reply helpful? Yes No
therush321 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

@Matthy79 , it works fine in the select statement but cannot pick up the fields to insert in my SQL table:

Was this reply helpful? Yes No
therush321 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

@Matthy79 I managed to get it to work:

Was this reply helpful? Yes No
Matthy79 4,178 Super User 2024 Season 1 on at

Like (0)

Report

Re: How do I get data from a JSON Scheme that are located in cells?

Looks good.

So is everything solved? Can you share a screenshot of the final flow setup?

Was this reply helpful? Yes No