Use JSON with big-number

[DSMejantel]

I use this code to generate a dropdowm menu :

select 
    'big_number'          as component;
select 
    count(distinct id) as value,
    'Photographies dans la base' as title,
    'blue'    as color,
    json_group_array(json_object(
      'label', tag_image,
      'link', 'album_vue.sql?tag='||tag_image )) as dropdown_item
    FROM image LEFT JOIN tag on image.id=tag.image_id;

But the dropdown is empty :

This is my debug :

{"component":"debug"}
{"value":2,"title":"Photographies dans la base","color":"blue","dropdown_item":"[{\"label\":\"cadre\",\"link\":\"album_vue.sql?tag=cadre\"},{\"label\":\"abeille\",\"link\":\"album_vue.sql?tag=abeille\"},{\"label\":\"fleur\",\"link\":\"album_vue.sql?tag=fleur\"}]"}

What is wrong from my part ?

[lovasoa]

Hi David !

The good news is that everything works as you would expect in SQLPage v0.30. Your exact code gives the following :

and the following debug values

{"component":"debug"}
{"value":2,"title":"Photographies dans la base","color":"blue","dropdown_item":[{"label":"tag 1","link":"album_vue.sql?tag=tag 1"},{"label":"tag 2","link":"album_vue.sql?tag=tag 2"}]}

This is thanks to the following change from the 0.30 release:

Easier json handling in databases without a native json type. SQLPage now detects when you use a json function in SQLite or MariaDB to generate a column, and automatically converts the resulting string to a json object. This allows easily using components that take json parameters (like the new columns component) in MariaDB and SQLite.

So, you have two options:

option 1

The easy option is to wait for a few days until I release SQLPage v0.30. Then you can just upgrade, and your existing code will work, without changing anything.

option 2

To work around the problem in sqlpage v0.29, you can use the dynamic component.
The properties passed to the dynamic component are always interpreted as json by sqlpage, even when passed as strings.
This is less intuitive and introduces some needless complexity, but has the advantage of working today, in sqlpage v0.29.

select
  'big_number' as component;

select
  'dynamic' as component,
  json_object (
    'title', 'Photographies dans la base',
    'value', count(distinct id),
    'color', 'blue',
    'dropdown_item', json_group_array (
      json_object (
        'label', tag_image,
        'link', 'album_vue.sql?tag=' || tag_image
      )
    )
  ) as properties
FROM image LEFT JOIN tag on image.id=tag.image_id;