Unable to parse Postgres query with json_object function

[SerialVelocity]

Describe the bug
Since 4.2, queries fail to parse that use the built-in Postgres functions. Looks like this was caused by #1263.

To Reproduce
Steps to reproduce the behavior:

1. Example SQL

SELECT json_object('{a, 1, b, 2}');
SELECT json_object('{{a, 1}, {b, 2}}');
SELECT json_object('{a, b}', '{1,2 }');

SELECT * FROM json_object('{a, 1, b, 2}');
SELECT * FROM json_object('{{a, 1}, {b, 2}}');
SELECT * FROM json_object('{a, b}', '{1,2 }');

2. Parsing this SQL using JSqlParser with these statements
3. Exception

Encountered unexpected token: "json_object" "JSON_OBJECT"

Expected behavior
The queries to parse as a function. This is a new bug as it used to work in 4.1

System

• Database you are using Postgres
• Java Version 11
• JSqlParser version 4.1 / 4.2

[manticore-projects]

JSON_OBJECT is a SQL standard compliant, special function and follows https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/img_text/json_object.html

JSON_OBJECT
  ( [ KEY ] string VALUE expr [ FORMAT JSON ]
      [, [ KEY ] string VALUE expr [ FORMAT JSON ] ]...
    [ JSON_on_null_clause ] [ JSON_returning_clause ]
  )

Your syntax is not standard compliant, Postgres specific json_object(keys text[], values text[]) and unsupported. (Before this did not matter when JSON_OBJECT has been parsed as any unknown function.)

A PR would be very welcome of course.

[SerialVelocity]

Yeah, implementing the SQL:2016 standard is good. It's just a shame backwards compatibility for existing functions was removed though when implementing it (especially since only Oracle supports the syntax so far afaik, and has been a built-in function in Postgres since way before that standard even existed).

[manticore-projects]

Ben,

nobody has removed anything. Before it has been incidentally "working" simply due to the fact that the function has not been parsed at all. Since we added the SQL:2016 standard compliant function and parse it, the Postgresql specific functions fails.

Since I have a lots of sympathy for PostgreSQL in general, lets work together on implementing a PostgreSQL specific exception here.

[SerialVelocity]

Hi Andreas,

Sorry if my message sounded a bit rude, I just meant the PR fixed Oracle, but broke a standard Postgres function. I think there was an issue somewhere where I discussed with wumpz about maybe selecting a customised parser for each database since the syntax (or reserved keywords) isn't always the same (e.g. array syntax) which might've helped for something like this. It is a quite a lot of work to implement though.

So how do we go about fixing this? I have no idea how JavaCC works. Is it something like adding K_JSON_OBJECT to RelObjectNameWithoutValue?

[manticore-projects]

Greetings.

No, worries, we are good.

On your first concern "Customized Parser for each RDBMS": I am working on a template based proto type.

On the "Prostgres compliant JSON_OBJECT function": I will try to implement this change next week and would return to you for testing when ready. As I said before: I definitely would like to achieve PostgreSQL support and compliance (although I never had the chance to use PostgreSQL as all our clients insist in Oracle).

[manticore-projects]

It has been a long flight and I still have jet lag and blurred eyes, so please give it a thorough testing and let me know anything missing. At least your provided samples should work. Cheers.