Answered

Spliting a JSON

(0) Share

Report

Posted on by Community Power Pla...

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

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... on at

Like (0)

Report

@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,951 Most Valuable Professional on at

Like (0)

Report

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... on at

Like (0)

Report

@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... on at

Like (0)

Report

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

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

Like (0)

Report
@Anonymous Maeby you clould try spliting your response.

split('yourJson','},')

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

Like (0)

Report
@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,865 Most Valuable Professional on at

Like (0)

Report

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... on at

Like (0)

Report

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... on at

Like (1)

Report

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