Group String in Array and Json

[Pablo_Sanchez (Pablo Sánchez Hernández)]

Hello folks,

let me explain my issue. I have an string like this:

[187597]xxxxxxxx // [66023]xxxxxxxxx // [187597]yyyyyyyyyy // [10068125]xxxxxx // [677203]xxxxx // [677203]yyyyyyyyyy // [10068125]yyyyyyyyyy //

I want convert it to Json, but is not the problem. The problem is that I want to group them by the “[number]”. I would need something like this:

{
	"187597": [
		"xxxxx",
		"yyyyy"
	],
	"66023": "xxxxx",
	"10068125": [
		"xxxxx",
		"yyyyy"
	],
	"677203": [
		"xxxxx",
		"yyyyy"
	]
}
 

So the problem is basically how can I group them by the value “[number]”. All the rest is fine (quotes, commas, tabs, etc.)

Any idea about this?

Thank you very much friends.

Kind regards,
Pablo

[Pablo_Sanchez (Pablo Sánchez Hernández)]

Actually I got this Json format, maybe its worth to work with this instead work with the original String:

		{
			"187597": "xxxx",
			"66023": "xxxx",
			"187597": "yyyy",
			"10068125": "xxxx",
			"677203": "xxxx",
			"677203": "yyyy",
			"10068125": "xxxx"
		}

[ppr (Peter)]

@Pablo_Sanchez

{
			"187597": "xxxx",
			"66023": "xxxx",
			"187597": "yyyy",
			"10068125": "xxxx",
			"677203": "xxxx",
			"677203": "yyyy",
			"10068125": "xxxx"
		}

after deserialization will look like this:

you will lose some values.

We can achieve it by grouping the values, but need the detail input string. Maybe you can share it as txt file with us? Thanks

[supermanPunch (Arpan)]

Hi @Pablo_Sanchez ,

I was able to Get the Data into the Json as Needed. Although there might be a better method than this.

First we have to identify the group of values in the input String. We could use the below Regex for that :

\[(\d+)\](\w+)

We can then use the Matches Activity with the above pattern to get the resultant matched values.

Here onwards, the data I have converted to a Datatable for ease in grouping the values.

Next, Group the data based on Number and iterate through the Grouped Values and add the Jobject or JArray as needed in the loop to a parent JObject.

Output would be in the Below format :

Check the workflow and Let us know if this doesn’t work :
ConvertString_ToJson.xaml (13.0 KB)

[Pablo_Sanchez (Pablo Sánchez Hernández)]

Hi @ppr

Thank you for the help. Here is the .txt input file.

Regards mate
inputText.txt (257 Bytes)

[Pablo_Sanchez (Pablo Sánchez Hernández)]

Amazing @supermanPunch

Thank you very much. Gonna try it and mark it as solution if I’m able to work with it. That’s exactly what I was thinking but I dont master REGEX. Lucky I find someone who does

Regards friend

[Pablo_Sanchez (Pablo Sánchez Hernández)]

@supermanPunch is almost perfect. Well, it is really perfect but I’ve made a mistake don’t telling you that the String has blank spaces

To explain me better, the initial string is not like “xxxxxxx”… is like “xxxx xxxxx xxxx xxxx xxx”, so using your .xaml i got this result

Could we fix this? Thank you again sir.
Regards,
Pablo

[supermanPunch (Arpan)]

Hi @Pablo_Sanchez ,

Try Replacing the Regular expression Pattern in Matches Activity with the below :

\[(\d+)\](.*?)\/\/

Let us know if it doesn’t work

[ppr (Peter)]

@Pablo_Sanchez
With the help of Regex we can split the string

refering to the groups (we will trim later)

For regex learning have a look here:

[CheatSheet] - System.Text.RegularExpressions | RegEx

This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented. Introduction From the namespace System.Text.RegularExpressions following methods are offered: Regex.Match Regex.Matches Regex.isMatch Regex.Replace Regex.Split A simple usage for example would look like this: [grafik] Recommendation: add System.Text.RegularExpressions to the imports: [grafik] it allows to use the shortened statement, as the namespace part can be ommited…

With following flow we can achieve the result:

The main idea is about:

• use regex to as mentioned above
  Regex.Matches(strText, strPattern, RegexOptions.IgnoreCase)

• group the matches and create JProperties, return all as an array

(From m In myMatches.Cast(Of Match).toList
Group m By k=m.Groups(1).Value.Trim Into grp = Group
Let arrVal = grp.Select(Function (a) a.Groups(2).Value.Trim).toArray
Let ja = JArray.FromObject(arrVal)
Select p = New JProperty(k, ja)).toarray

• Create the JObject from the returned JProperty Array
  new JObject(arrProps)

We had used LINQ:

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

But can also decompose the LINQ part to essential activities

Find starter help here:
ppr_RegexBox_PabloSanchez.xaml (7.0 KB)

[Pablo_Sanchez (Pablo Sánchez Hernández)]

Thank you very much guys, I wish I could give 2 solutions xD

That’s what makes UiPath big… its community!

Thanks thanks and thanks

Regards,
Have a nice day guys.