JSON manipulation

[bartbutenaers]

Dear,

I was wondering if there is any way to use Jsonata for changing Json data, or returning a transformed version of the JSON data (containing the changes).

For example assume following input:

{
    "TOPIC1": {
        "last_value": "old value"
    },
    "TOPIC2": {
        "last_value": "old value"
    }
}

That needs to be transformed to following output (based on some Jsonata expression):

{
    "TOPIC1": {
        "last_value": "old value"
    },
    "TOPIC2": {
        "last_value": "new value"
    }
}

Since Jsonata is a query and transformation language (and not a manipulation language), I assume it is not possible... I have been debugging the entire expression evaluation, but couldn't find anything relevant.

But perhaps there is a workaround e.g. using a user defined function (that logs everything from the original data, except from the changes )?

Thanks in advance for your time !!!
Bart Butenaers

[blgm]

@bartbutenaers, that's a really good question. Am I right in thinking that you want to iterate over the key/value pairs in an object, apply some kind of logic, and aggregate the return the result as an object? I think @andrew-coleman or @mattbaileyuk would be the best people to answer that.

[bartbutenaers]

Hi George,

thanks for your response!

Yes indeed, currently I can e.g. get all the values at once using a simple powerful Jsonata expression:

However it would be great if a similar expression could be used to update the JSON data (i.e. update the original JSON data or return a transformed copy of the original JSON data). For example *.last_value := 'new value' to get following result:

Don't think this is currently possible with this library, but I may be mistaken ...

Kind regards,
Bart

[andrew-coleman]

JSONata was designed to be a functional language in that the output of an expression (or part thereof) is dependent only on its input, and has no side effects (including the modification of the input). There is no guaranteed order of execution of parts of an expression, and the engine is free to process parts of the input data in parallel (although the current javascript implementation doesn't do this). The := operator is a variable binding operator rather than a general assignment operator that you have in imperative languages. The latter would modify the state of an object and break the functional paradigm.

This functional design was inspired by XQuery which has very similar semantics for querying and transforming XML data. XQuery does have an extension language called XQuery Update Facility which introduces extra concepts and syntax for modifying the input data (adding, removing & changing values). It does this without breaking the functional programming model by introducing the concept of a Pending Update List which is returned by the expression and then applied to the input data. If there is sufficient interest, then I could investigate how we might do this with JSONata.

[bartbutenaers]

Hello Andrew,

thanks for your feedback!
Hopefuly the "Jsonata Update Facility" will be added to the roadmap in the near future ...

Kind regards,
Bart

[shrickus]

I, too, have been looking for ways to update selected values within a JSON structure while leaving the rest unchanged. Something similar to using an XSLT template to copy every element and attribute in an XML document as-is, with the exception of those that match a certain XPATH selector...

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
   
   <xsl:output method="xml" indent="yes" />
   
   <!-- Copy everything -->
   <xsl:template match="/ | @* | node()">
       <xsl:copy>
            <xsl:apply-templates select="* | @* | node()" />
       </xsl:copy>
   </xsl:template>
   
   <!-- Update something -->
   <xsl:template match="Product[@weight > 10]/@surcharge">
         <xsl:attribute name="surcharge">
               <xsl:value-of select="number(.) + 10" />
         </xsl:attribute>
    </xsl:template>

</xsl:stylesheet>

This would be very useful when e.g. adding a surcharge to all the Products in my Order where Weight > 10. Although I can use a JSONata expression to locate and operate on those Products, I don't see a good way to leave all the other objects/attributes/levels unaltered. The new $merge() function coming in 1.3 will help with modifying each matching Product, but will not help with keeping the enclosing Order structure intact.

There seem to be several attempts at what is loosely called JSONT (templating rules for JSON) available in github and npm. I guess I'm not really suggesting that they could be incorporated into the JSONata engine - but it seems that a similar utility transforming one JSON structure into another based on JSONata expressions would be very useful (and satisfy the OP's initial use case).

Steve

P.S -- IF something like this were to be made available, PLEASE don't follow suit and call it "JSONTata" ;*)

[andrew-coleman]

Interesting. I need to do some thinking on this and play around with a few options. Thanks for all the discussion - keep it coming!

[andrew-coleman]

@bartbutenaers @shrickus As promised, I've done some thinking and come up with a proposal that I hope will meet this requirement. In branch 1.4 I have added some new syntax allows you to copy an object with modifications applied to the copy. The modifications are defined using a location path to select the part(s) of the object that should be changed followed by a structure to define the changes. The syntax uses the vertical bar | to separate the parts of the expression in a similar manner to regular expressions using a slash '/' to separate the /search/replace/ terms. Think of it like |search|replace| for objects.

Example: |Account.Order.Product|{'Price': Price * 1.2}| defines a transform that will return a deep copy the object passed to it, but with the Product object modified such that its Price property has had its value increased by 20%. The first part of the expression is the path location that specifies all of the objects within the overall object to change, and the second part defines an object that will get merged into the object(s) matched by the first part. The merging semantics is the same as that of the $merge() function.

This transform definition syntax creates a jsonata function which you can either assign to a variable and use multiple times, or invoke inline.
Example: payload ~> |Account.Order.Product|{'Price': Price * 1.2}|
or: $increasePrice := |Account.Order.Product|{'Price': Price * 1.2}|
This also has the benefit that multiple transforms can be chained together for more complex transformations.

In common with $merge(), multiple changes (inserts or updates) can be made to an object.
Example: |Account.Order.Product|{'Price': Price * 1.2, 'Total': Price * Quantity}|
Note that the Total will be calculated using the original price, not the modified one (JSONata is declarative not imperative).

I've also been thinking how to support the removal of properties from objects. The merge semantics doesn't work for this, so I'm proposing to add an optional extension to the second part of the transform expression where you can specify which properties to delete (by name). This would be in the form of a string or array of strings.
Example: $ ~> |Account.Order.Product|{'Total': Price * Quantity}, ['Price', 'Quantity']|
This copies the input, but for each Product it inserts a Total and removes the Price and Quantity properties.

I'd welcome feedback on any of this including the usability of the syntax. Just grab the 1.4 branch.

[shrickus]

Looks like you've done more than just "some thinking"...

I have to admit, my first impression was that the new | search | replace | syntax was too hard to read -- and this comes from someone who likes building complex regex expressions for fun! Actually, the idea is growing on me... but it would help me if we could discuss the shortcomings of using more established JSONata syntax to accomplish the same results (if possible).

What does that even mean? Well, let's take your first example that increases the prices of all products by 20%. The two things we need for that are:

1. an expression that locates all the Product objects, and
2. an expression of what parts of each Product will be modified (updated or deleted) and how the new value will be determined

Without the new syntax, and using an imaginary $replace() function, I would expect to write something like this:
$replace(Account.Order.Product, { "Price": Price * 1.20 }) or
Account.Order.Product~>$replace({ "Price": Price * 1.20 })

and since you agree that the replacement action is essentially a merge operation, why not this?
$merge(Account.Order.Product, { "Price": Price * 1.20 })

Implementation details aside, I would think any number of updates/deletes could be defined as:
Account.Order.Product~>$replace({ "Total": Price * Quantity, "Price": undefined, "Quantity": undefined })

Since you are proposing the new search/replace syntax, I'm assuming the above is insufficient
SO, if the new syntax IS adopted, I'd like to see it be generally available for all input data types, not just for replacing bits of JSON objects. Perhaps this is already your intention, in which case please take this as affirmation of your design ;*)

In your opinion, which of these alternate usages would make sense, and which would be problematic or already have better solutions?

# Update incorrect text within the Product Names
Account.Order.Product."Product Name" ~> | "Trilbey" | "Trilby" |

# Round up all Product weights from lbs to whole oz.
Account.Order.Product.Description ~> | Weight | $ceiling(Weight * 16) |

# Rename an object field, retaining its value
Account.Order.Product.Description ~> | Colour | { "Color": $ } |

# Use a function to aggregate child object field values onto the parent
Account.Order.Product ~> | Description.$max([Height,Width,Depth]) > 500 | { "Oversized": true } |

I realize some of these are a bit contrived, and perhaps all of these could be rewritten to work properly using the current implementation -- I guess I'm thinking that the returned data type of the search side would be used to figure out how to apply the replace results. And the search context would be applied to the incoming results on the left of the ~> chain operator. I think this would mean that the new syntax represents a function with 3 inputs: context, search, and replace.

Sorry for the length of this -- and if you got this far, thanks for reading it!

Steve

[andrew-coleman]

This is great feedback - thanks for taking the time!

So to address the first part of it -- why not just have a $transform() function? Perhaps something like:
$transform(payload, Account.Order.Product, {'Price': Price * 1.2})
instead of:
payload ~> |Account.Order.Product|{'Price': Price * 1.2}|

• The first argument payload is required because that is the object that we want to copy (and modify)
• The second argument needs to point to the position in the payload object that should be modified. The problem is that the function arguments are evaluated before the function is invoked (eager evaluation) and so Account.Order.Product will be evaluated relative to the parent context rather than the result of evaluating payload.
• The same issue affects the third argument - Price * 1.2 will be evaluated relative the the parent rather than relative to each Product.

The only way I can think of doing this using functions alone, is for the $transform function to take callback functions as its second and third arguments so that their evaluation can be delayed and invoked with the right context. Something like:

$transform(payload, 
           function($obj) {
             $obj.Account.Order.Product
           },
           function($match) {
             $merge($match, {'Price': $match.Price * 1.2})
           })                

(I haven't tried this BTW, I'm just making it up as I go along).

It might be good to provide this higher-order function as well. In the same way we have a $sort function as well as the 'order-by' syntax.

Comments on the |...|...| syntax:

I'm also slightly worried the syntax might be confusing - I'm open to any better ideas.
I did try <search | replace>, but I thought that looked even more confusing when mixed with the ~> operator.

# Update incorrect text within the Product Names
Account.Order.Product."Product Name" ~> | "Trilbey" | "Trilby" |

In this case the transform construct is being handed the Product Name which is just a string. So it could only return a string - it's lost the reference to the outer object that we might want to deep copy. Same with the other examples. But I take your point about wanting to be able to change a single value without having to merge an object. I will give that some more thought.

Thanks again for the thoughtful feedback.

P.S - Interesting to see that you used #... to add comments to your examples. JSONata doesn't yet support comments, but if it did, would you prefer # to //?

[bartbutenaers]

Hi Andrew,

I thought that my question would be classified in the section 'maybe later, maybe never'. But as Steve already mentioned, this is indeed much more than only thinking ... Thank you very much already for all the effort !!!!!!!!! I really appreciate it.

I will start experimenting with it next weekend, more specifically in a Node-Red context. Since Jsonata became integrated into Node-Red, a lot of custom function nodes could be already replaced by standard nodes containing simple Jsonata expressions (to read data). However still lots of custom function nodes are still being used, just to write values in JSON objects.

I would be very pleased if such Jsonata transforms could also be integrated somehow into Node-Red.

E.g. integration in the Change Node. To allow it copying the message payload (A) to some location(s) inside some global/flow/msg JSON object (B), using some new syntax:

I know that Nick (@knolleary) and Dave(@dceejay) are already overloaded, but I'm pretty sure they could have very valuable input for your transform syntax. At least for my purpose :-) Sorry guys for mentioning! If you don't like the idea of integrating this syntax in node-red, I will try to wrap a contribution around it. But I would prefer to have it some day inside at least the Change node.

Not clear to me at this moment if my setup is even possibly, since you are returning a deep clone of the original object (instead of changing the original object).

Will follow this topic with great interest, and will come back on this...

Kind regards,
Bart

[shrickus]

Andrew,
I did not know about the "eager evaluation" used by the function processing, so thanks for that explanation. If the | search | replace | syntax would help avoid all the callback function code, then I'm all for it! And as I said, it's starting to grow on me -- it's different enough from the rest of the punctuation in use that it stands out, and does look like a regex in a way. Incidentally, that's specifically why I did NOT choose // to denote a comment, and the # is not being used yet that I've seen. But hey, you got my point, eh? It would indeed be nice to be able to comment some of my more heinous expressions...

The most important bit (IMO) kind of got lost at the tail end of my last rant -- that there need to be 3 inputs, which I'm calling context, search, and replace. The context (typically the expr before the chain operator, or $$ if none is given) would be used as $ during the evaluation of the search expression. The results of the search expression would be used as $ during the replacement evaluation. That may be a bit simplistic, especially when the search matches an array -- does $ refer to the whole array, or each element?

Ooh, now that I'm thinking about it, if the context is missing, would it be possible to implement a deep search (similar to the XSLT descendant axis, where **/Product finds all Products at any level)? Feel free to tell me that's a bridge too far... but it would enable some interesting options, like:

# Add a domain field to each email record
| eMail | { "Domain": $substringAfter(eMail, "@") } |

Essentially, it's like Unix pipes, that transform the data flowing from step to step -- you even chose the | symbol. Coincidence? I don't think so! Maybe this is where your "streaming" jsonata idea comes into play? Maybe we should treat the pipe symbol in JSONata to mean "stream the preceeding results into the next expression evaluator" -- create an evaluator for each piece, and hook the output of each one into the input of the next one. Then we could put as many expressions together as necessary, instead of limiting it to a "search" step and a "replace" step.

Bart,
I don't see any problems with integrating this new feature into the node-red change node. The current implementation simply evaluates the JSONata expression, gets the resulting JS objects, and replaces the changed variable with this output. Whether it's a deep clone or not should not matter.

I'm with you -- this is such an interesting topic, and could have huge impact on msg transformations within node-red. In fact, in my case it already has... this new feature just simplifies the expressions we have to build when we need to keep most of the msg structure intact, but change selected parts of it.

Steve

[knolleary]

Some quick responses that are a bit of a muddle between the jsonata specific feature being discussed and how it may apply in the context of node-red.

I'm not 100% sure on the |...|...| syntax - but I don't have an instinctive sense of what would be clearer. Harking back to my Perl days, the regex syntax of /.../.../ is a bit more familiar - although given we aren't talking regexes here, reusing that bit of the syntax may only serve to confuse.

As per the screenshot in @bartbutenaers screenshot in the previous comment, the Change node's current UI separates out the 'which value you want to change' and 'what value you want to set it to' into separate inputs. Currently the latter can be an expression (and thus dynamic) - but the former (the 'where') can only be a hardcoded value. The proposed syntax combines the where and what into a single expression - so the current UI would not work. Given we're now talking about side-effects of expressions now, this would have to exposed as another option in the change node to evaluate an expression against the message and take the result as the new message to pass on.

But there are some challenges with applying this to node-red. When applied to pure JSON objects, deep copying the original is fine. When applied to a node-red msg object, there may well be properties that cannot be deep copied (such as an HTTP Request object). We already have to special case some of this stuff when we clone messages within the runtime. We can't have JSONata deep cloning node-red message objects.

Applying it to global/flow context is a non starter - it isn't an object that can be manipulated like this.

On the matter of comments, we rely on the fact we can strip newline characters from a JSONata expression without modifying its meaning - this is because we have to present the expression in both a multi-line text editor and a single line text input (which strips newlines). As soon as you add single-line comments we won't be able to do that and we'll have to come up with a whole new UI for expressions (our problem, not yours...). Of course, comments are an important feature. If you're considering # vs // I'd urge consistency with JavaScript -// (or the multiline /* ... */ which would avoid the newline issue...)

---

Expand this bit for my current thinking on how the Change node will allow the 'where' to be determined dynamically. It doesn't involve JSONata so I'm collapsing into a details block so it doesn't derail the thread

Going back to the Change node's Set action which separates out the where and what - most use cases we've seen for making the 'where' dynamic would be solved if we added mustache support to that field.

For example, if you had an object of sensor readings in flow context:

flow.sensors = {
   "temperature": 123,
   "humidity": 456,
   "pressure": 789
}

and you receive a message with a new sensor reading:

msg = {
   "topic": "temperature",
   "payload": 246
}

The Change node could then be configured to set flow.sensors.{{topic}} to the value of msg.payload. The mustache syntax would be evaluated as flow.sensors.temperature.

From a user perspective - much easier to understand given our use of mustache elsewhere.

[andrew-coleman]

Steve,
I think we are mostly agreeing with each other, but I just want to try and clarify a few points to make sure we are on the same wavelength. The current implementation in branch 1.4 has the following structure:
head ~> | location | update |
I've intentionally changed all the terminology here in an attempt not to overly compare it with regexes.

• head evaluates to the object that is to be copied and transformed
• location evaluates to the part(s) within the copied object that are to be updated. The location expression is evaluated relative to the result of head. At the moment the result of evaluating location must be of type object, but we can revisit this.
• update evaluates to an object that is merged into the object matched by the location. update is evaluated relative to the result of location and if location matched multiple objects, then the update gets evaluated for each one of these. The result of (each) update is merged into the result of location.

The ~> operator is an existing operator for function chaining and passes the value on the left hand side to the function on the right hand side as its first argument. The expression on the right hand side must evaluate to a function, hence the |...|...| syntax generates a function with one argument.

The following illustrates the effect of moving the location path progressively from the location to the head. They all work on the same input data:

The result of the expression on the left hand side of the ~> determines the top level of the object that is going to be transformed.

Your observation about doing a 'deep search' transformation is definitely possible, and what I had in mind for this. E.g. **.Product ~> |...|...|

On the subject of 'pipes', that was why I chose to make it generate a function so that you could specify a pipeline of transformations using the ~> operator:
... ~> |...|...| ~> |...|...| ~> |...|...|
and because ~> does proper function composition, you can build complex higher-order functions very simply:

(
  $transform1 := |...|...|;
  $transform2 := |...|...|;
  $transform3 := |...|...|;
  $complex_transform := transform1 ~> transform2 ~> transform3;

  $complex_transform(my_obj)
)

[andrew-coleman]

@knolleary just to pick up on a few of your points:

• The syntax |...|...| might be confusing. I don't disagree, but I can't think of anything better at the moment. I'll keep thinking though.
• Your comment "Given we're now talking about side-effects of expressions now, ...", just to clarify, JSONata has no side effects. The transform expression will not modify the input in any way. Or perhaps I'm misunderstanding what you meant.
• "We can't have JSONata deep cloning node-red message objects". Okay, that's good to know. We could make the deep copy code pluggable so that Node-RED (and others) could provide an optional callback function to the jsonata engine that gets invoked every time a deep copy is requested. End users won't see this or have to worry about it.