Answered

Spliting a JSON

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello!

I have issue. I know it's possible however I don't know how to do it. I need to use parse JSON on the file which output is:

[
 {
 "@odata.etag": "",
 "ItemInternalId": "cd4c2aed-d994-4ecb-a2e1-35561b406356",
 "Atribute": "Personal Nr 1st",
 "Value": "1234567"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "31494ec9-c2a4-4ee6-9ee1-56157f1ab4b4",
 "Atribute": "Name and Surname",
 "Value": "Name Surname"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "c125057a-32db-4932-af6e-d8a16b126645",
 "Atribute": "Personal Nr 2nd",
 "Value": "12312123123"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "9343ed0a-1b20-470e-9517-0f1788b88a53",
 "Atribute": "Identificator",
 "Value": "ABC 123456"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "06ac6b99-d7a1-4214-9250-a0a5820bfbcf",
 "Atribute": "Area",
 "Value": "XYZ"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "4bc05628-65b8-4a83-bf8b-5800831276d5",
 "Atribute": "HR AREA",
 "Value": "PP4"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "639a7c4a-1aab-4fef-9492-96314e4a1ac2",
 "Atribute": "Job",
 "Value": "Job Name"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "e322dfa7-9c59-42c3-9130-81d145acbc1e",
 "Atribute": "Workhours",
 "Value": "168"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "0c3f05dc-b4f6-486a-9872-05369f9dfea0",
 "Atribute": "sallary",
 "Value": "1234"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "c1427832-10cd-4f0e-a799-2fa0c66b2fae",
 "Atribute": "Employment date",
 "Value": "12.12.2020"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "17376bd3-9b0a-4136-bb56-247d28a7cec4",
 "Atribute": "adress",
 "Value": "unknown"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "eef1a4ba-6aac-42d8-9e58-abe8625e4c10",
 "Atribute": "house no",
 "Value": "X"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "4f5afec6-a2d5-4da5-b96d-20f5f2b15954",
 "Atribute": "ZIP",
 "Value": "01-234"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "3b379bd0-5974-413d-83c2-1c90fa29b45f",
 "Atribute": "City",
 "Value": "CityName"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "d2d45610-b3b5-4383-a8a9-9d844c587f51",
 "Atribute": "Contract type",
 "Value": "ContractName"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "5472b543-e770-4501-b4f3-3f0ec8d8ae7b",
 "Atribute": "Starting date of current contract",
 "Value": "12.12.2019"
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "67f02231-cca0-4ee3-952d-b4d8ab3e06da",
 "Atribute": "End date",
 "Value": "31.12.2022"
 }
]

The sample from this JSON is:

{
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "@@odata.etag": {
 "type": "string"
 },
 "ItemInternalId": {
 "type": "string"
 },
 "Atribute": {
 "type": "string"
 },
 "Value": {
 "type": "string"
 }
 },
 "required": [
 "@@odata.etag",
 "ItemInternalId",
 "Atribute",
 "Value"
 ]
 }
}

And the problem starts now. The "Atribute"s aren't changing and I would like to get JSON data not as 'atribute' and 'value'.

I would like to get each element separated like: 'Personal nr 1st','Personal nr 2nd' etc. Also in some cases like name and surname I would like to know how this "Atribute" can be split into 'Name' and 'Surname'.

I would be very grateful for help.

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

What you need to do is loop (apply to each) for each element in JSON and based on attribute value add conditions based on the 'attribute' . If your JSON data is always going to be in same format it will be easy.

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@DeepakS
However I need to edit JSON schema yes? Can you tell me how? I'm new to JSON. Parse JSON have to be at loop yes?

I would be grateful for telling me how to do it because I have a lot of flows with inputs which have same problem. I were doing that till now by a lot of additional flow steps. I want to miniaturize my flows.

Was this reply helpful? Yes No
ChristianAbata 8,953 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

hi @Anonymous when you use parse json you can see as variables the objects of you json.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@ChristianAbata

It's not a solution. You done the same thing as I was. You have atribute and value and next to get what I need I have to filter array to get only one value which atribute I know.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Generaly I parse excel table. I'm geting columns. I need rows. Easiest explanation ever 😄

Was this reply helpful? Yes No
ChristianAbata 8,953 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@Anonymous Maeby you clould try spliting your response.

split('yourJson','},')

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@ChristianAbata

You mean add to JSON schema somehow or you mean compose and expression?
split('dynamicelement-yourJson','},')
EDIT: If you mean dynamic element etc it's not working and also I don't have any idea how it could work.
split(body('Parse_JSON'),'},')

I pasted my input and schema because I need accurate answers. I'm very fresh to this. Otherwise I won't understand what you mean 🙂

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

Like (0)

Report
Copy link

Link copied!

Hi @ChristianAbata

Hi

Is your sample data is from excel flow action List rows present in a table. Then you can use the following way to access each attribute names.

body('List_rows_present_in_a_table')?['value'][0]['Atribute']
The above expression returns 'Personal Nr 1st'

body('List_rows_present_in_a_table')?['value'][1]['Atribute']
The above expression returns 'Name and Surname'

body('List_rows_present_in_a_table')?['value'][2]['Atribute']
The above expression returns 'Personal Nr 2nd'

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

It's not the solution. I asked how to access to rows by JSON. That 'compose' gives as result 'Personal Nr 1st'.
Thats not what I need.
1. I need all object. Because for name and surname I want to define this to split into name and surname. It's one sample I have to split a lot of JSON objects.
2. One block will give me all the results. For me its work around because to get this result I have to create X blocks to get result of 1.
3. It's not efficient when I have input with 30-50 rows. 30-50 additional blocks.
4. For more efficient work with that data I want to get dynamic element which title is 'atribute' and the value. When I will know how to do it I will miniaturize my flow by 60-70%

@abm So coming back to the point can you explain me how to modify JSON schema to that result? I tried to learn that by myself however I have a lot of issues and helping me with that one small sample can help me a lot.

Thanks for your help 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Btw your way is interesting and it can help me a lot. For example when I want to get value i need to use
body('List_rows_present_in_a_table')?['Value'][1]['Value'] and can you tell me how to select Name or Surname only? I have to split that?
I use concat(split(body('List_rows_present_in_a_table')?['Value'][1]['Value'], ' ')[1]) maybe someone know shorter expression?

Was this reply helpful? Yes No