field creation

[gregsdennis]

An interesting question was asked on StackOverflow, and subsequently on my lib's repo, which I thought would be a good suggestion for the V2 spec (supposing that happens).

Proposal

Given the JSON document

{
  "options": [
    {
      "price": 217,
      "quantity": 2
    },
    {
      "price": 63,
      "quantity": 5
    }
  ]
}

This proposal enables performing a calculation, e.g. price * quantity, and getting its result by assigning a new property in the local JSON so that it can be queried by subsequence segments.

A path that could do this would be:

$.options[?(@.result = @.price * @.quantity)].result

In this path, @.result is assigned the result of the multiplication. This assignment would operate as an "upsert" where it would overwrite any value that may already be there. The new value is subsequently returned by the next segment .result.

This likely has applications beyond this simple example and is worth considering. Additionally, many other query languages (e.g. SQL) permit the creation and selection of temporary values like this.

It's understood that filter expressions as currently defined are required to return a boolean value, and the above expression does not, so that aspect of filter expressions will need to be revisited/addressed.

Depending on how the conversation goes here, I could probably work it up in my implementation as an optional behavior to make it available on my playground.

Caveat

This feature is predicated on math operations being supported, which the base spec does not. Currently we can't even select based on calculated values meeting some threshold, e.g.

$.options[?(@.price * @.quantity > 400)]

Note that these math operators (not assignment, though) are already optionally supported on my playground.

[cabo]

Hi Greg,

in relational algebra, the ability to select a subset of the columns of the rows is called projection. In SQL, projection confusingly happens in the the SELECT part of a query. SQL SELECT adds the ability to add columns (e.g., by computing them) to projection, quite similar to what you are proposing (but then you are missing the original projection that is about selecting columns).

We decided to limit JSONPath-base to selection (confusingly the part that happens in the WHERE clause in SQL). We may want to address projection in a future extension. If yes, I think we should do this in a simple but powerful way (e.g., including the ability to cut down on columns, not just adding them). I think your proposal is a way to experiment with projection. Coming up with an expression language that can do more powerful operations on values than JSONPath can do today will be another component of such a proposal. A way to present results that include values that are not in the original query argument will be another.

[gregsdennis]

I think projections in general would be a very appreciated feature that could potentially lead to something like transformations (which I have also seen requested).

[danielaparker]

I think projections in general would be a very appreciated feature that could potentially lead to something like transformations (which I have also seen requested).

@gregsdennis I don't think it makes sense to talk about projections in JSONPath as if they had anything to do with projections in SQL. SQL is based on relational algebra, which provides its theoretical foundation. JSONPath has no such foundation, it's an ad hoc construction. Relational algebra is closed under the operators, that is, the input to any operator is a relation, and the output is a relation, which allows expressions to be nested in an arbitrary manner. Expressions in IETF JSONPath do not appear to be closed under anything.

Regarding transformations, it's not obvious why you would want to introduce that into JSONPath, as there are other well established JSON query languages that have that, notably JMESPath, JSONAta, and XPATH 3.1. JSONPath's unique feature is that it returns nodes that correspond to location/value pairs in the original document, which has its own utility, particularly in implementations that support update in place in the original document.

[danielaparker]

In this path, @.result is assigned the result of the multiplication. This assignment would operate as an "upsert" where it would overwrite any value that may already be there. The new value is subsequently returned by the next segment .result.

It sounds to me like this would be a use case for lexical scoping. JMESPath recently accepted a JMESPath Enhancement Proposal for lexical scoping using a new let expression.