Search through different json files, and find the same value

Help Studio
, , , , , , ,
1

I have a lot of different json files, and i want to extract some specific values from these json files.

Example, i have these 3 different files, json1, json2, json3.

for json1 i want do extract line:

105 “text”: “Områdeleder”,
196 “id”: “28b6f0d9-3d70-498d-b01e-5e7aac8693d8”,
197 “title”: “Administration”,
200 “customId”: “48”,
205 “managerId”: “13e4a594-363e-41cb-b227-7cdfed9d95ba”,
407 “id”: “13e4a594-363e-41cb-b227-7cdfed9d95ba”,

for json2 i want do extract line:

110 “text”: “Senior IT Arkitekt”,
440 “id”: “3afd5778-dca2-40a2-907a-0f96a2a8b716”,
441 “title”: “BI og Analyse”,",
444 “customId”: “47”,
449 “managerId”: “4f941a88-3a76-491d-a089-6bae8b2d4292”,
552 “id”: “4f941a88-3a76-491d-a089-6bae8b2d4292”,

for json3 i want do extract line:

228 “text”: “Chef Shared Service”,
163 “id”: “4a1de04c-a72e-4b67-8b4f-8dfda1ff7ac0”,
164 “title”: “IDS”,
167 “customId”: “9”,
172 “managerId”: “34842a53-5668-4048-bb14-5bda41bc5115”,
457 “id”: “34842a53-5668-4048-bb14-5bda41bc5115”,

is this possible, too loop each json array, and only get out what you need?

json2.json (18.2 KB)

json1.json (12.6 KB)
json3.txt (14.6 KB)

1 Like
2

Hello @christian.bendtsen

I have recently asked similar questions regarding searching in JSON.
This has now succeeded in my case, so perhaps you can get som useful information from these two posts:

Best regards,
Soren

3

My problem is, that i have a lot of “id”, “text”, “managerId” in each json file, but thanks for the help anyway! :slight_smile:

4

You can iterate through each file
Read text activity you can use and than you can deserialise the response.
You can store the required key value pair into some data table or excel or based on the format you need.

5

I have already tried that :slight_smile:, but i only need these specific values i have listed above in each json file, my problem is, that the json files are all different, and the values are moving around.

6

Create data table before the for each loop and have the field name as column and append values by if else or switch case
Issued for each loop you can have switch case and if for each json key name is the id you want than update the result into data table

7

Hello @christian.bendtsen

If you have something to look for in the JSON, to determine excactly which “id” or “text” you need, then I believe that is what my previous posts are about.

You can define search queries for the JSON such as:

jobj_JsonObject.SelectToken("..[?(@.text =~ /^Chef/)]")

This will grab the jToken that is named “text” and starts with “Chef”.

Regards
Soren

8

Hej Søren :slight_smile:

My problem is, that i don’t know what the value is. It could me “chef” or something else. The same for the id, the value here is a uuid, that will change for each json file

9

Can you show an example of this? I have already tried that

10

If you know the ID beforehand, then you can search for “text” based on this.

Assign str_ID = "d9d69bf1-fca7-436e-b2cf-3f5d2a0d475d"

jobj_JsonObject.SelectToken("..[?(@.id== '"+str_ID.ToString+"')].text")("text").ToString

Regards
Soren