Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLPage throws on postgres json_object #708

Open
bever1337 opened this issue Nov 24, 2024 · 4 comments
Open

SQLPage throws on postgres json_object #708

bever1337 opened this issue Nov 24, 2024 · 4 comments
Labels
bug Something isn't working

Comments

@bever1337
Copy link

bever1337 commented Nov 24, 2024

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 bever1337 added the bug Something isn't working label Nov 24, 2024
@bever1337 bever1337 changed the title SQLPage doesn't comprehend json with postgres SQLPage throws on postgres json_object Nov 24, 2024
@bever1337
Copy link
Author

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
Copy link
Collaborator

lovasoa commented Nov 24, 2024

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
Copy link
Author

bever1337 commented Nov 24, 2024

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
Copy link
Collaborator

lovasoa commented Nov 24, 2024

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

apache/datafusion-sqlparser-rs#1546

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants