CODEWITHSUNDEEP
mysqljsondatabase
user8966659
user8966659

Reputation:

find json by key in mysql

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,
}

Upvotes: 0

Views: 2258

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 1

Related Questions