SQLPage throws on postgres json_object

[bever1337]

Introduction

SQLPage throws on json_object

To Reproduce

A simple form component with one input child. Its child is a select with options. The example is borrowed from the postgres docs, but the keys are changed to match SQLPage's expected types.

https://www.postgresql.org/docs/current/functions-json.html

SELECT
  'form' AS component,
  'Reproduction example' AS title;

SELECT
  'select' AS type,
  json_agg(json_object('label' VALUE 'P123', 'value' :
    'Jaws')) AS options,
  TRUE AS required;

The first SELECT returns:

component, title
form, Reproduction example

The second SELECT returns:

type, options, required
select, [{"label" : "P123", "value" : "Jaws"}], true

Actual behavior

After following these steps, what happened ? If you saw an error on the command line or inside your page, then paste it here

[2024-11-24T17:40:42.076Z ERROR sqlpage::render] SQL error: Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:
    
      'select' AS type,
      json_object('label' VALUE 'P123', 'value' :
                         ⬆️
        'Jaws') AS options,
    
    
    Caused by:
        0: sql parser error: Expected: ), found: VALUE at Line: 36, Column: 23
        1: SQLPage could not parse and prepare this SQL statement

The json_build_object works, probably because the syntax is not postgres-specific:

SELECT
  'select' AS type,
  json_agg(json_build_object('label', 'P123', 'value',
    'Jaws')) AS options,
  TRUE AS required;

Expected behavior

SQLPage is sql-engine agnostic. SQLPage supports (ignores) postgres json functions.

Version information

• OS: MacOS
• Database Postgres
• SQLPage Version sqlpage 0.30.1

[bever1337]

I apologize for the constant edits to this issue. I was trying to write a reproduction that wasn't specific to my codebase, and some details got lost when I was using examples from sqlpage and the postgres docs. The comment should be good now

[lovasoa]

Hello and thank you for the report ! We'll get this postgres-specific syntax implemented in the sql parser. In the meantime, as you already figured out, you can use json_build_object.

[bever1337]

Postgres is so notoriously difficult to parse that even pgformatter struggles to keep pace 😂 Thank you for the prompt reply. Twice in as many days!

[lovasoa]

I opened a PR upstream; it's a small fix, the same syntax is already supported for mssql.

https://github.com/apache/datafusion-sqlparser-rs/pull/1546