0

I am exposing JSONPaths for advanced queries in my application, and sometimes it would be really convenient to chain expressions as I'd do for example with jq. Example:

{
  "foo": [
    {
      "bar": "bar",
      "from": 10,
      "to": 20
    }
  ]
}

$.foo[*] ? (@.bar == "bar") | @.to - @.from

Now I see that the | operator as used above is not implemented, and in pure SQL I could store the result of the first part in a table / variable. However in application I pass the JSONPath to jsonb_path_query and it would be convenient if I didn't have to chain the invocations manually but just let PostgreSQL handle it all in one pre-baked statement. Is there anything that would let me do that?

EDIT: I could do that using e.g.

SELECT jsonb_path_query((
   SELECT jsonb_path_query('{ ... }', '$.foo[*] ? (@.bar == "bar")')
), '$.to - $.from');

but that would mean that I'd need to split the JSONPath query (user input) - exactly what I don't want to do in the app.

Radim Vansa
  • 5,686
  • 2
  • 25
  • 40
  • What do you mean by "*in pure SQL I could store the result of the first part in a table / variable.*"? – Bergi Jan 12 '22 at 20:56
  • 1
    So the question is if I understand well: is it possible to write the above `jq` query using SQL ? – zakaria amine Jan 13 '22 at 10:15
  • @zakariaamine Not really. Full `jq` syntax aside, the question is whether it is possible to do it within single instance of `jsonpath` type, so that I can pass the param to `jsonb_path_query` function. – Radim Vansa Jan 14 '22 at 13:31
  • @RadimVansa "*the question is whether it is possible to do it within single instance of `jsonpath` type*" - I guess you already answered that yourself in the question, stating that `|` is not implemented? Or are you looking for a different way of writing this particular example, i.e. `$.foo[*]?(@.bar=="bar").to - $.foo[*]?(@.bar=="bar").from`? – Bergi Jan 14 '22 at 13:32
  • @Bergi That would work since this example selects single numeric value, but had there be more matching items, the `-` wouldn't work as vector operation. And I would be concerned if Postgres smart enough to not perform the lookup twice. – Radim Vansa Jan 14 '22 at 13:43
  • Yes, I tend to know answer for what I am exactly asking but there might be some way around it, or syntax I just didn't get from the few examples in documentation. – Radim Vansa Jan 14 '22 at 13:44
  • I don't believe there are json path operators that are implemented but not documented. Nesting `jsonb_path_query` calls is exactly the way to go, and sql result sets nicely mirror the streaming semantics of `jq` – Bergi Jan 14 '22 at 13:45

0 Answers0