how to "join" objects based on a common key

[darrencruse]

Apologies I'm new to JSONata hopefully a dumb question and I just saw there is a slightly similar but not identical question to mine here:
#275

Our use case is we will have JSON with arrays from different REST apis whose entries we will need to match based on a common key and then transform them together into a single array of combined objects (although not just merge them actually pick certain properties or otherwise transform the property values so slightly different than #275 above which only merged the objects).

(also our goal in using JSONata is to have something a little friendly for non/semi-technical users and I'm hoping maybe there's a simpler solution than #275 which involves using a function we were hoping our use of JSONata would avoid that)

The test example I created in the JSONata Exercisor is as follows:

{
	"Employee": [{
			"SSN": "496913021",
			"FirstName": "Fred",
			"Surname": "Smith"
		},
		{
			"SSN": "496737199",
			"FirstName": "Darren",
			"Surname": "Cruse"
		}
	],
	"Contact": [{
			"ssn": "496913021",
			"Phone": [{
					"type": "home",
					"number": "0203 544 1234"
				},
				{
					"type": "office",
					"number": "01962 001234"
				},
				{
					"type": "mobile",
					"number": "077 7700 1234"
				}
			]
		},
		{
			"ssn": "496737199",
			"Phone": [{
					"type": "home",
					"number": "3146458343"
				},
				{
					"type": "mobile",
					"number": "315 782 9279"
				}
			]
		}
	]
}

So I was trying to match the array elements by the social security number ("SSN" in Employee and "ssn" in Contact).

This I thought I was getting close with but could never get it to work right:

$.Employee.{
    "SSN": $.SSN,
    "name": FirstName & " " & Surname,
    "phone": $$.Contact[ssn=$.SSN].Phone[type="mobile"].number	
}

But I can't seem to get the Contact to match by the Employee SSN I get:

[
  {
    "SSN": "496913021",
    "name": "Fred Smith"
  },
  {
    "SSN": "496737199",
    "name": "Darren Cruse"
  }
]

UPDATE: Just played a little more looking at the #275 solution and I did get this to work:

Employee.{
    "SSN": SSN,
    "name": FirstName & " " & Surname,
    "phone": ($$.Contact ~> $filter(function($contact){$contact.ssn = SSN})).Phone[type="mobile"].number	
}

Still wondering is this the simplest it can be?

If so then I'm wondering could I abstract it away somehow? e.g. Could I hide that filter behind a custom function I would provide something like:

Employee.{
    "SSN": SSN,
    "name": FirstName & " " & Surname,
    "phone": $matchKey($$.Contact, "ssn", SSN).Phone[type="mobile"].number	
}

should that be possible?

[jugaadi]

Will this ($$ ~> | Employee | ($ssn := SSN;$$.Contact[$ssn = $.ssn]),['ssn'] |).Employee work?

[darrencruse]

Thanks jugaadi yes your suggestion works

I've been pouring over the docs trying to understand it...

I see this is key right:
https://docs.jsonata.org/control-operators#transform-

Actually thinking more about your approach and how you used the "$ssn = SSN" assignment I realized my original attempt almost was working it was really a "scoping problem" in me not getting at the outer "SSN" of Employee within the square brackets of "$$.Contact[ssn=$.SSN]".

i.e. I just tried this and it works:

Employee.{
    "SSN": SSN,
    "name": FirstName & " " & Surname,
    "phone": ($ssn := SSN; $$.Contact[ssn=$ssn]).Phone[type="mobile"].number	
}

I kind of thought that was my problem but I couldn't find any way to "navigate up".

i.e. I'm not sure if it's from when I did XQuery years ago or something else but I had imagined something like this (note the two dots) should have worked but it doesn't:

Employee.{
    "SSN": SSN,
    "name": FirstName & " " & Surname,
    "phone": $$.Contact[ssn=$..SSN].Phone[type="mobile"].number	
}

Ah just found another variation that works (illustrates the same point about the "scoping"):

Employee.{
    "SSN": SSN,
    "name": FirstName & " " & Surname,
    "phone": (function($ssn){ $$.Contact[ssn = $ssn] })(SSN).Phone[type="mobile"].number	
}

Still hoping for a cleaner solution (if there's not couldn't/shouldn't something like the "$.." be added to JSONata? if only as syntax sugar for something like these workarounds?)

(whoops just noticed $.. means match anywhere in JSONPath so maybe that's a bad choice of syntax I dunno but just something/anything more concise more declarative would be nice)

[andrew-coleman]

Thanks for raising this, and for the discussion. A declarative syntax for expressing this type of 'join' operation is definitely something that I want to add to JSONata. I've been thinking for a while on how best to do this. XQuery supports 'join' using its FLWOR expression, which has quite a verbose syntax. I'd love to support the semantics of the FLWOR expression, but with the more terse syntax of JSONata.

A brief sidetrack to explain my thinking on this:---

• XPath 1.0 has a minimal, terse syntax - very easy to learn, fairly powerful but with limitations.
  E.g. Contact.Phone[type="home"].number
• XQuery, in its early days had aspects in common with XPath, but was not aligned with it. There was a quest to 'align' the two languages resulting in XPath 2.0 and XQuery 1.0 where XQuery became a superset of XPath. At that point, some of the verbose (XQuery-like) syntax was made available to XPath (including a limited form of FLWOR), resulting in two different styles of selecting data, the following being equivalent to the one liner above:

for $phone in Contact.Phone
where $phone.type = "home"
return
  $phone.number

• XPath also adopted the data model which brought the idea of 'sequences' to the language (something I've readily adopted in JSONata). This allows expressions such as fn:sum(Invoice.Item.(Price * Quantity)) to be written where sub-expressions become fully composable - a huge enhancement over XPath 1.0.

In JSONata, to a certain extent I've tried to imagine how XPath would have evolved if it had restricted itself to its terse syntax. The 'join' semantic is one remaining language structure that's missing. I just need to figure out what the syntax should be.

[Allam76]

This is a common problem in API consumption and I solve it by "extending" JSONata by sending the input through a proxy before sending it to JSONata. The proxy has a handler that is then called for each property. Check my other posts for examples.

Then, a json hyper schema is connected and provide config to pull resources from different APIs. Finally a small client is used to actually do the work.

The join happens in the link between the two APIs. So the APIs must be designed to play together. I'm in the business world, so this is usually the case.
Otherwise one could envision a target transformation of the output of one of the APIs or a middleware or something but that depends on the use case.

On reason graphQL is popular, is how it solves this problem. Also odata is designed with this in mind. I'm sure there are other smart REST based ideas in the realm of API gateways..

/Martin

[Allam76]

Isn't this the same as the parent problem? #299

[andrew-coleman]

Not necessarily. A parent operator could certainly be used to help 'program' a join operation, but it's not as 'declarative' as a FLWOR / SQL JOIN expression, as requested. I see both this and #299 as valid but separate requirements.

[Allam76]

OK. Could this be useful? If you have not already seen it. jsoniq
Also something on FLWOR to expression
Common in the business world: FEEL

[jugaadi]

1. How does join work in the context of JSON(object/array)? What algorithms are used in XPath/XQuery, etc for this operation?
2. How is it different from $merge or transform operator?
3. How will we solve the conflict with $join() in terms of terminology?

[janvda]

It is closed but what would be the jsonata query that solved the problem described in this issue ?

[andrew-coleman]

In this case, the following expression will perform the join:

Employee@$E.Contact@$C[$E.SSN = $C.ssn].{
    "SSN": $E.SSN,
    "name": $E.FirstName & " " & $E.Surname,
    "phone": $C.Phone[type="mobile"].number
}

https://try.jsonata.org/-RPtLYB-