MySQL JSON strings

Avatar
  • updated
  • Completed

Hello,

Flyspeed is reporting an error when switching between SQL Text and Query builder,
"Invalid SELECT statement, unexpected token ">>" at line 13, pos 16"

The query runs however.

I am extracting strings from JSON structures, where the field user.prefs contains
something like:


{"notes": {"global_notifs": "none"}, "mailinglist": {"members": "0", "announce": "0", "members-post": "0"}}

SELECT
    user.id,
    user.custid,
    user.username,
    user.name,
    user.email,
    user.prefs,
    cust.name AS cust_name,
    cust.autsys,
    cust.status AS cust_status,
    inf.id AS inf_id,
    inf.name AS inf_name,
    user.prefs->>'$.mailinglist.members' as is_members,
    user.prefs->>'$.mailinglist.announce' as is_announce
FROM
    user user
    INNER JOIN cust cust ON user.custid = cust.id
    INNER JOIN virtualinterface vir ON vir.custid = cust.id
    INNER JOIN vlaninterface vla ON vla.virtualinterfaceid = vir.id
    INNER JOIN vlan vlan ON vla.vlanid = vlan.id
    INNER JOIN infrastructure inf ON vlan.infrastructureid = inf.id
WHERE
    cust.dateleave IS NULL AND
    cust.status = 1

I've tried various way to escape the ">>" but can't think of how to make both MySQL and Flyspeed happy.

Any thoughts?

Version: 4.0.1.1
Release date: 01 February 2022
License: Express

Avatar
Andrey Zavyalov, PM
  • Completed

Hello, 

We've added support of JSON-handling operators syntax in the latest version.

Avatar
Andrey Zavyalov, PM
  • Planned

Sorry for the late response. We will fix this issue soon.