Parsing JSON in MariaDB with SQLPage

[julesbouton]

Hi !
I just discovered SQLPage this morning, and first of all, thank you for this nice tool 👍

I'm working with MariaDB, and I'm struggling with the JSON files produced by a checkbox in a form component. The doc states that I can use :

the JSON_TABLE function in MySQL (which you'll need to wrap in a function, because SQLPage cannot parse the non-standard syntax of this function)

I see perfectly the problem (SQL page cannot parse this syntax), but I cannot understand what is meant by 'wrapping this in a function'... (I am also quite new to Mariadb).

So any help / suggestion is welcome. Actually, I would just need to rewrite in mariadb/mysql the following piece of code from the doc :

insert into best_fruits(id)
select CAST(value AS integer) as id
from *json_each*($preferred_fruits);

[lovasoa]

Hello and welcome to SQLPage !

I added an example to the documentation which hopefully illustrates your use case: https://github.com/lovasoa/SQLpage/tree/main/examples/mysql%20json%20handling

insert into group_members(group_id, user_id)
select CAST(json_unquote(json_elems.json_value) AS INT), last_insert_id()
from (
    with recursive json_elems(n, json_value) as (
        select 0, json_extract(:Memberships, '$[0]')
        union all
        select n + 1, json_extract(:Memberships, concat('$[', n + 1, ']'))
        from json_elems
        where json_value is not null
    ) select * from json_elems where json_value is not null
) as json_elems
where :Memberships is not null;

Let me know if it works for you.

In the long term we will support the json_table syntax, which will make this less cumbersome to write (at least in the most recent versions of MySQL and MariaDB).

What I initially meant was something like

CREATE PROCEDURE insert_group_members_json (in userid INT, in group_members_json TEXT)
 BEGIN
  INSERT INTO group_members(user_id, group_id)
  select userid, CAST(json_val AS INT)
  from json_table(
  	group_members_json,
    "$[*]" COLUMNS(json_val VARCHAR(255) PATH "$")
  ) as json_t;
 END;

but it would also be impossible to call from sqlpage

[julesbouton]

Great, thanks a lot, I will give it a try in the next days, but I now see what you meant. Basically, we need to emulate json_table with other functions. This is very useful :)

[lovasoa]

I wrote the code for parsing JSON_TABLE expressions, we are now just waiting for it to get merged and for sqlparser to make a new release: apache/datafusion-sqlparser-rs#1062

[julesbouton]

I tried it, and it works perfectly, thanks :) Thanks for the very quick and efficient answer and for submitting it to sqlparser !

[lovasoa]

SQLPage v0.18.3 was just released, with support for MySQL's JSON_TABLE syntax, which makes it way easier to handle json data with MySQL in SQLPage.

[julesbouton]

Nice, thank you very much !

[lovasoa]

I updated the example to take into account the newly supported syntax: https://github.com/lovasoa/SQLpage/tree/main/examples/mysql%20json%20handling

insert into group_members(group_id, user_id)
select group_name, last_insert_id()
from json_table(:Memberships, '$[*]' columns (
    group_name int path '$'
)) as json_elems