Markus G. Markus G. - 1 year ago 80
SQL Question

SQL JSON Column (IgnoreCase)

I have a SQL column in JSON Format which looks like

and I wanted to ask if it's possible to make an Ignore Case to this column like

select * from TYPE_hugo_DATA WHERE UPPER(data->'$.name') = UPPER('hugo');

Answer Source

The issue is that you make the equals on the raw json value, which contains the quotes(").

If you want to retrieve the json value without the quotes you can either do ->> is the short syntax for the JSON_UNQUOTE(JSON_EXTRACT()):

select * from TYPE_hugo_DATA WHERE UPPER(data->>'$.name') = UPPER('hugo');


select * from TYPE_hugo_DATA WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) = UPPER('hugo');