Get last three chars of a json element in mySQL column-CodePudding

I have json data in a column called settings:

{"AreaAdmin":null,
"StatsAreaAdmin":null,
"Logo":"1664805113094.svg",
"ExportFree":null,
"Banner":null,
"BannerMobile":null,
"BannerRetina":null,
"BannerAddr": null
}

I have figured out how to grab a json element:

SELECT *
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

How do I grab the last three chars of an element? I want to check if all Logo are svg or not. I've tried

SELECT SUBSTRING(JSON_EXTRACT(settings,"$.Logo"), LEN(JSON_EXTRACT(settings,"$.Logo")-1,3))
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

But get Query 1 ERROR: FUNCTION uc_common.LEN does not exist

Doing

SELECT RIGHT(JSON_EXTRACT(settings,"$.HQLogo"),3)

gives me vg"

CodePudding user response：

I got it using

SELECT SUBSTRING(JSON_EXTRACT(settings,"$.Logo"),-4,3 )
FROM `user`
WHERE JSON_EXTRACT(settings, "$.Logo")

CodePudding user response：

You can try and test this:

SELECT RIGHT(JSON_UNQUOTE(JSON_EXTRACT(settings,'$.Logo')),3) FROM user;


 -------------------------------------------------------- 
| RIGHT(JSON_UNQUOTE(JSON_EXTRACT(settings,'$.Logo')),3) |
 -------------------------------------------------------- 
| svg                                                    |
 --------------------------------------------------------