Getting value without quotes from json array turned into a table

Help Query Tuning
1

Hi there

I sometimes need to iterate over a json array and do so by using table(json_to_array(...)). However, when I want to use the value, it is often wrapped in quotes, due to the value being a string. When I wish to utilize the value, it’s an issue that’s it’s wrapped in quotes. I’ve tried a multitude of ways, to get the actual value, like json_extract_string(table_col, '.'), but with no luck.

As a small example, I’ve got some URLs I need to look through and I’m getting multiple substring to search for:

SELECT links.url,
       x.table_col
  FROM (SELECT 'https://www.singlestore.com/hello' url FROM dual
         union all
        SELECT 'https://www.singlestore.com/world' url FROM dual
       ) links
  JOIN TABLE(JSON_TO_ARRAY('["hello", "cats", "dogs"]')) x
    on (instr(links.url, x.table_col) > 0);

The above query returns no rows, but I would expect to get a row for https://www.singlestore.com/hello.

So what I usually end up doing, is building a new json object and then extracting the value: json_extract_string(json_build_object('val', x.table_col), 'val')), but I really do believe that this is not the best way to go about it. Doing that however does make the query work:

SELECT links.url,
       x.table_col
  FROM (SELECT 'https://www.singlestore.com/hello' url FROM dual
         union all
        SELECT 'https://www.singlestore.com/world' url FROM dual
       ) links
  JOIN TABLE(JSON_TO_ARRAY('["hello", "cats", "dogs"]')) x
    on (instr(links.url, json_extract_string(json_build_object('val', x.table_col), 'val')) > 0);

+-----------------------------------+-----------+
|                url                | table_col |
+-----------------------------------+-----------+
| https://www.singlestore.com/hello | "hello"   |
+-----------------------------------+-----------+

I would really like if there was a way to do json_extract_string(table_col, '.') or maybe just table_col::$. Maybe there already is, but I’ve just failed to find it in the documentation.

2

You could treat the json as a string and use SUBSTR or a UDF to strip the quotes off when you reference table_col.

singlestore> set @j = ("\"foo\"" :> json);
Query OK, 0 rows affected (0.01 sec)

singlestore> select @j;
+-------+
| @j    |
+-------+
| "foo" |
+-------+
1 row in set (0.00 sec)

singlestore> select substr(@j, 2, length(@j) - 2);
+-------------------------------+
| substr(@j, 2, length(@j) - 2) |
+-------------------------------+
| foo                           |
+-------------------------------+
1 row in set (0.01 sec)

To make it briefer, you could wrap the SUBSTR call and cast in a UDF.

3

I most certainly could do all sort of fancy things to remove the quotes. Substr, trim, replace etc., but they’re all additional operations to perform. Is the reason you’re suggesting substr, because you know that it has the best performance?

I would assume that since SingleStore is having support for table(json_to_array(...)), that they would also allow for a way of using json_extract_string or the shorthand key paths to extract the actual value. Is there a place to submit feature requests?

1 Like