Markus G. Markus G. - 3 months ago 8
SQL Question

SQL JSON Column (IgnoreCase)

I have a SQL column in JSON Format which looks like

data->'$.name'
and I wanted to ask if it's possible to make an Ignore Case to this column like
UPPER(data->'$.name')
.

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

Answer

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');

or

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