find json by key in mysql

Question

I have a column of profiles in which is json. How to search all fields to search for a particular Nick. Nick can be in utf-8, so the regular expression is not suitable for the solution.

{
  "nick1": {
    "color": [
      1,
      1,
      1
    ],
    "highlight": [
      1,
      1,
      1
    ],
    "global": false,
  },
  "nick2": {
    "color": [
      1,
      0.15,
      0.15
    ],
    "highlight": [
      0.2,
      1,
      0.2
    ],
    "global": true,
  },
  "other_nick": {
    "color": [
      1,
      1,
      1
    ],
    "highlight": [
      1,
      1,
      1
    ],
    "global": false,
}

"can be in utf-8, so the regular expression is not suitable"...what leads you to that conclusion? — ADyson, Aug 31 '18 at 10:09
That implies you're not storing / processing the data as UTF8 within the database? Is the database in ANSI or something and then you're storing encoded UTF8 strings inside it? It's not clear why the scenario you've just described would occur. If you want to store UTF8 data in your database, it would make sense to allow the database to support it properly, and then you can just use a regex on the values normally. — ADyson, Aug 31 '18 at 10:34
@ADyson, MySQL's regular expression implementation didn't support matching utf8 characters (see https://stackoverflow.com/questions/19774618/mysql-regex-utf-8-characters) before MySQL 8.0. They recently changed the library they use to implement regular expression matching, so in MySQL 8.0 it works better. — Bill Karwin, Sep 02 '18 at 17:01
@BillKarwin Interesting, I didn't know that, thanks. I admit to being more of a SQL Server regular. — ADyson, Sep 02 '18 at 18:39

Bill Karwin · Accepted Answer · 2018-09-20T18:09:45.627

First of all, your JSON is malformed. You can't put a comma after the last element in an object.

  "global": false,
},

should be

  "global": false
},

And you're missing a closing bracket near the last lines:

    "global": false,
}

should be

    "global": false
  }
}

So I suppose you are not using MySQL 5.7's JSON data type. If you were, it would not allow you to save malformed JSON. It would give this error:

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 138 in value for column '...'.

Once I corrected the format, I was able to test your JSON.

mysql [localhost] {msandbox} (test) > create table j (j json);

mysql [localhost] {msandbox} (test) > insert into j set j = '...your json...';

Then I could extract the entry for a given nick:

mysql [localhost] {msandbox} (test) > select j->'$.nick2' from j;
+------------------------------------------------------------------------+
| j->'$.nick2'                                                           |
+------------------------------------------------------------------------+
| {"color": [1, 0.15, 0.15], "global": true, "highlight": [0.2, 1, 0.2]} |
+------------------------------------------------------------------------+

If I try to extract the path for a key that doesn't exist, I get NULL:

mysql [localhost] {msandbox} (test) > select j->'$.nick3' from j;
+--------------+
| j->'$.nick3' |
+--------------+
| NULL         |
+--------------+

The -> operator is supported in MySQL 5.7 and later. It can also be accessed as function JSON_EXTRACT(). See https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

You might say, "I'm using MySQL 5.6, how can I do the same thing?"

If you want to use JSON in MySQL, you should upgrade to 5.7 or later. There is no support for JSON in MySQL 5.6 or earlier versions. If you want to see the amount of effort it would take to parse JSON in MySQL 5.6, look at the answers here: How to get values from MySQL(5.6) column if that contains json document as string and notice it takes several people's answers to get a function that finally works.

I suggest it is less work to upgrade to MySQL 5.7 than to tear your hair out using MySQL 5.6 to parse JSON with such arcane stored functions.

find json by key in mysql

1 Answers1