cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajarac
Super User
Super User

Please help with JSON data

Hello there!

 

I hope someone could give an advice here:

 

I have my JSON structure coming form my PowerApps, the JSON is valid but I am having difficulties trying to understand how to get the values and map with my excel file. I am using the plumsail connector and basically I need:

 

1.  For the template data in the Create XLSX document from template what is the right syntax here?

2. I need to extract some individual values from my JSON string, how to do this?

 

Below some screen shots and the JSON Schema.

 

thanks in advance for any help.

 

Regards,

 

JSON Structure1.PNGJSON Structure2.PNGJSON Structure3.PNG

 

 

{
"type": "array",
"items": {
"type": "object",
"properties": {
"Quote1": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Bid": {"type": ["string", "null"]},
"BName": {"type": ["string", "null"]},
"Price": {"type": ["number","null"]},
"Quantity": {"type": ["integer","null"]},
"Title": {"type": ["string", "null"]},
"UoM": {"type": ["string", "null"]}
}
}
},
"Quote2": {
"type": "array",
"items": {
"type": "object",
"properties": {
"BidT2": {"type": ["string", "null"]},
"BNameT2": {"type": ["string", "null"]},
"PriceT2": {"type": ["number","null"]},
"QuantityT2": {"type": ["integer","null"]},
"TitleT2": {"type": ["string", "null"]},
"UoMT2": {"type": ["string", "null"]}
}
}
},
"Header": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Created": {"type": ["string", "null"]},
"Customer": {"type": ["string", "null"]},
"Quote": {"type": ["integer","null"]},
"Reg": {"type": ["string", "null"]}
}
}
}
}
}
}

6 REPLIES 6
mahoneypat
Dual Super User
Dual Super User

Try initializing your variable as an "Object" instead of a "String".

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


sajarac
Super User
Super User

Hi,

Thanks for the help. After switching to Object, now I am getting this:

 

sajarac_0-1615331536510.png

 

Sorry. My bad.  It does need to be a string.  Can you share the input JSON string instead of the schema to help troubleshoot?

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Dual Super User
Dual Super User

The nulls in the Quantity and UOM fields was causing a schema validation error.  Please try it with this schema instead.  I changed it so Quantity accepts values of integer or null, and UOM accepts string or null.  FYI that I also removed those two from the Required part of the schema.  Also make sure any nulls in your JSON are all lower case (Null will cause error).

 

Regards,

Pat

 

{
  "type""array",
  "items": {
    "type""object",
    "properties": {
      "AEPAQuote1": {
        "type""array",
        "items": {
          "type""object",
          "properties": {
            "BidItem": {
              "type""string"
            },
            "BuildingName": {
              "type""string"
            },
            "Price": {
              "type""number"
            },
            "Quantity": {
              "type": [
                "integer",
                "null"
              ]
            },
            "Title": {
              "type""string"
            },
            "UoM": {
              "type": [
                "string",
                "null"
              ]
            }
          },
          "required": [
            "BidItem",
            "BuildingName",
            "Price"
          ]
        }
      },
      "AEPAQuote2": {
        "type""array",
        "items": {
          "type""object",
          "properties": {
            "BidItemT2": {
              "type""string"
            },
            "BuildingNameT2": {
              "type""string"
            },
            "PriceT2": {
              "type""number"
            },
            "QuantityT2": {
              "type""integer"
            },
            "TitleT2": {
              "type""string"
            },
            "UoMT2": {
              "type""string"
            }
          },
          "required": [
            "BidItemT2",
            "BuildingNameT2",
            "PriceT2",
            "QuantityT2",
            "TitleT2",
            "UoMT2"
          ]
        }
      },
      "Header": {
        "type""array",
        "items": {
          "type""object",
          "properties": {
            "Bond": {
              "type""number"
            },
            "Created": {
              "type""string"
            },
            "Customer": {
              "type""string"
            },
            "Freight": {
              "type""number"
            },
            "Quote": {
              "type""integer"
            },
            "Region": {
              "type""string"
            }
          },
          "required": [
            "Bond",
            "Created",
            "Customer",
            "Freight",
            "Quote",
            "Region"
          ]
        }
      }
    },
    "required": [
      "AEPAQuote1",
      "AEPAQuote2",
      "Header"
    ]
  }
}




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hey, thank you very much we are in a good track.

 

Now I am getting Type type type.

 

sajarac_0-1615338038632.png

 

mahoneypat
Dual Super User
Dual Super User

After the parse json step, I added a CSV table step to test it out.  See pics below on how to start drilling into your schema.  The schema has an odd structure, so it may be a challenge.

 

mahoneypat_1-1615342806357.png

 

 

mahoneypat_0-1615342779083.png

 

If this solve it for you, please mark it as solution.  Kudos are appreciated too.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,280)