Error with json_table in mariadb.

[xylle]

Introduction

Error with json_table in mariadb.

To Reproduce

Query work correctly with phpmyadmin. The SQL code comes from the page : https://sql.datapage.app/blog.sql?post=JSON%20in%20SQL%3A%20A%20Comprehensive%20Guide

SELECT jt.*
FROM JSON_TABLE(
  '["Alice", "Bob", "Charlie"]',
  '$[*]' COLUMNS(
    row_num FOR ORDINALITY,
    name VARCHAR(50) PATH '$'
  )
) AS jt;

Actual behavior

Not working.

[2024-10-30T15:31:47.813Z ERROR sqlpage::render] SQL error: Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:

      '$[*]' COLUMNS(
        row_num FOR ORDINALITY,
                   ⬆️
        name VARCHAR(50) PATH '$'


    Caused by:
        0: sql parser error: Expected: PATH, found: ORDINALITY at Line: 40, Column: 17
        1: SQLPage could not parse and prepare this SQL statement

Screenshots

Expected behavior

row_num	name
1	Alice
2	Bob
3	Charlie

Version information

• OS : Windows 10
• Database : Mariadb
• SQLPage Version : 0.29.0

[lovasoa]

Thank you for reporting this, @xylle !

I proposed a fix for this in the sql parser we are using: apache/datafusion-sqlparser-rs#1493
We are now just waiting for it to be merged and for a new version of the parser to be released.

In the meantime, you can use a window function instead to count row numbers:

SELECT 
  jt.*,
  row_number() OVER () AS row_num
FROM JSON_TABLE(
  '["Alice", "Bob", "Charlie"]',
  '$[*]' COLUMNS(
    name VARCHAR(50) PATH '$')
) AS jt

[xylle]

I was trying to understand how JSON works. SQL and JSON are obscure to me.
Thanks

[lovasoa]

The fix was merged in the sql library. It's coming soon to sqlpage