parse error in `FOR JSON PATH`

[lovasoa]

Discussed in #126

^{Originally posted by Pieter3033 November 8, 2023}
Hi,

The newest form component is really cool 🔥.

I am currently struggling to populate the options of a select type in the form component.
I am using MSSQL.

I have attempted the following:
(SELECT Column1 AS label, Column2 AS value FROM MyTable FOR JSON PATH) AS options

Error:

Note, I have tested this SQL statement inside of MSSQL and the output is as expected.
Maybe I am struggling with the syntax?

Regards

[lovasoa]

I reported the issue upstream to the sql parser library.

In the meantime, you have two options:

• format the json string manually using string manipulation functions
• define a custom function that returns your json string, and just call that function from your SQL page (SELECT create_options_json() AS options)

[lovasoa]

I wrote a fix for the parser, waiting for it to get merged: apache/datafusion-sqlparser-rs#1043

[Pieter3033]

Hi @lovasoa, hope you are well.

Love the new update 🔥

I see that the sqlparser has been merged, has it been pushed in SQLPage 0.16.0 ?

[lovasoa]

Hi ! I'm fine, thank you :)

My pull request has been merged, but the new sqlparser still hasn't been released. I'll publish a SQLPage release after the sqlparser release, which @alamb should do beginning of next week.

[Pieter3033]

Perfect.

Thank you for the feedback.

[lovasoa]

Good news for you !
Just merged the upstream change, we have FOR JSON in the latest development version of SQLPage.

You can try it now on docker with lovasoa/sqlpage:main, or wait for the big release which is coming in a few days max.

[Pieter3033]

Perfect thank you for the update, looking forward to the next SQLPage update.

[lovasoa]

It's going to be a big one, with some long awaited features :)