mysql: using json data and not hating it
Tags: mysql, json
Published: 2024-05-29 12:51:40

it's a universal truth that one of the primary jobs of backend developers is drinking from the firehose of json the frontends are constantly spraying at us. normally, we pick apart all those key/value pairs and slot them into the neatly-arranged columns of our db, keeping our database structure nice and [normal](https://datasciencehorizons.com/database-normalization-a-practical-guide). but sometimes there are good reasons to just store a whole json object or array as an undifferentiated blob. 

in the dark, old days, this json would go in a `text` column, which was fine if all we needed to do was store it and return it. but if we needed to do something more complex like, extract certain values from that json or, god forbid, use one in a `WHERE` clause, things could get out of hand pretty quickly.

fortunately, modern mysql supports `JSON` as a native data type, allowing us to work with json data in a way that's <i>almost</i> pleasurable. in this post we're going to go over extracting values from json and using json data in `WHERE` clauses.

## creating a `JSON` column
let's start by creating a table with a column of type `JSON`.

```sql
CREATE TABLE `some_data` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `some_json` json NULL,
  `some_text` text NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

the test data we're going to be using is the json representation of bratmobile's 'kiss and ride' single[https://www.discogs.com/master/409458-Bratmobile-Kiss-And-Ride].

```json
{
  "artist": "Bratmobile",
  "title": "Kiss and Ride",
  "format": "single",
  "year": 1992,
  "label": "Kill Rock Stars",
  "tracks": {
    "A": [
      {
        "title": "Kiss and Ride",
        "duration_seconds": 88
      }
    ],
    "B": [
      {
        "title": "No You Don't",
        "duration_seconds": 105
      },
      {
        "title": "Queenie",
        "duration_seconds": 79
      }
    ]
  }
}
```

we'll insert that json into both a native `JSON` type column as well as into a `TEXT` column. we'll come back to that text col later.

```sql
INSERT INTO some_data VALUES(
  null,
  'some name',
  -- as json
  '{ "artist": "Bratmobile", "title": "Kiss and Ride", "format": "single", "year": 1992, "label": "Kill Rock Stars", "tracks": { "A": [ { "title": "Kiss and Ride", "duration_seconds": 88 } ], "B": [ { "title": "No You Don\'t", "duration_seconds": 105 }, { "title": "Queenie", "duration_seconds": 79 } ] } }',
  -- as string
  '{ "artist": "Bratmobile", "title": "Kiss and Ride", "format": "single", "year": 1992, "label": "Kill Rock Stars", "tracks": { "A": [ { "title": "Kiss and Ride", "duration_seconds": 88 } ], "B": [ { "title": "No You Don\'t", "duration_seconds": 105 }, { "title": "Queenie", "duration_seconds": 79 } ] } }'
);
```

## extracting some data from that json
now that we have some json in our `JSON` column, we can work on selecting out individual values. we do this with mysql's `JSON_EXTRACT` function.

`JSON_EXTRACT` takes two arguments: the column and the path of the element you want to select. paths are separated by a dot, kind of the same way directory paths are separated by a `/`, and the top level of the path is denoted by `$`. if you've ever struggled with [`jq`](https://jqlang.github.io/jq/), you should feel comfortable struggling with this.

in this example, we select the `artist` element from the top level of our json object.

```sql
SELECT  JSON_EXTRACT(some_json, '$.artist') as artist
FROM    some_data
WHERE   id = 1;
+--------------+
| artist       |
+--------------+
| "Bratmobile" |
+--------------+
```

mysql also provides the `->` operator as a shorthand for `JSON_EXTRACT` to help make our queries a little cleaner.

```sql
SELECT  some_json -> '$.artist' as artist
FROM    some_data
WHERE   id = 1;
+--------------+
| artist       |
+--------------+
| "Bratmobile" |
+--------------+
```

an annoyance we notice immediately with these results is that they're quote-enclosed. we don't want this. nobody wants this. let's remove them with the function `JSON_UNQUOTE`

```sql
SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist
FROM    some_data
WHERE   id = 1;
+------------+
| artist     |
+------------+
| Bratmobile |
+------------+
```

the shorthand `->` operator we used before can be modified to `->>` to automatically includ `JSON_UNQUOTE`. using this operator is the same as enclosing a call to `JSON_EXTRACT` in `JSON_UNQUOTE`:

```sql
SELECT  some_json ->> '$.artist' as artist
FROM    some_data
WHERE   id = 1;
+------------+
| artist     |
+------------+
| Bratmobile |
+------------+
```

## dealing with arrays
getting single elements from the top level is great, but we're also going to want to deal with arrays.

we can reference arrays in the path we pass to `JSON_EXTRACT` by using an index inside square brackets. this is pretty familiar stuff. for instance, in our sample data we have an object called `tracks` that contains two arrays keyed as `A` and `B`; these are the songs on side 'a' and 'b'. those `A` and `B` elements are arrays of song objects. if we wanted to get the first song of side `B`, we would construct our `JSON_EXTRACT` call like so:

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks.B[0].title') as side_b_song_one
FROM    some_data
WHERE   id = 1;
+-----------------+
| side_b_song_one |
+-----------------+
| "No You Don't"  |
+-----------------+
```

looking at the path argument, we can see we start at the top of the hierarchy with `$`, then reference the `tracks` object. next is the array `B`. we indicate we want the first element of this array with `[0]`, then continue our path by giving the key `title`. the result is the title of the first song on side b.


if we want _all_ the songs on side b, we can replace that index with the wildcard `*`. this returns a json array of titles.

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks.B[*].title') as side_b
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b                      |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+
```

we could also use the `**` wildcard to do this job. 

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks.B**.title') as side_b
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b                      |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+
```

it's important to note that `**` actually means _all_ the paths between the prefix and the suffix. this means that we can use it to get all of the `title` elements under `tracks`, regardless of whether there in the array keyed `A` or `B` like so:

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks**.title') as side_a_and_side_be
FROM    some_data
WHERE   id = 1;
+----------------------------------------------+
| side_a_and_side_be                           |
+----------------------------------------------+
| ["Kiss and Ride", "No You Don't", "Queenie"] |
+----------------------------------------------+
```

in that example, `**` matches both `A` and `B`. very powerful stuff.

we can also use ranges when defining our array indexes. for instance, if we wanted to get all the `title`s on side `B` between position zero and one, we could use the index `[0 to 1]`:

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks.B[0 to 1].title') as side_b_first_two
FROM    some_data
WHERE   id = 1;
+-----------------------------+
| side_b_first_two            |
+-----------------------------+
| ["No You Don't", "Queenie"] |
+-----------------------------+
```

and if all we want is the last element of an array, mysql allows us to use the literal word `last`:

```sql
SELECT  JSON_EXTRACT(some_json, '$.tracks.B[last].title') as side_b_last_track
FROM    some_data
WHERE   id = 1;
+-------------------+
| side_b_last_track |
+-------------------+
| "Queenie"         |
+-------------------+
```


### using this in `WHERE` clauses
extracting data from json is useful but, really, we could just select the whole column and run the data through `json_decode` in our controller. the _real_ money is using this in `WHERE` clauses. let's look:

```sql
SELECT  JSON_EXTRACT(some_json, '$.title') as title
FROM    some_data
WHERE   JSON_EXTRACT(some_json, '$.year') = 1992;
+-----------------+
| title           |
+-----------------+
| "Kiss and Ride" |
+-----------------+
```

here we just extracted `year` from our json column and used in a `WHERE` clause.

## a kludge to simulate `WHERE IN` clauses
if we want to write a `WHERE IN` clause against an array in our json object, things get a bit trickier. our initial ideal might be to simply try using `IN` on an array extracted from our json:

```sql
-- this absolutely DOES NOT WORK
SELECT  JSON_EXTRACT(some_json, '$.title') as title
FROM    some_data
WHERE   "No You Don't" IN JSON_EXTRACT(some_json, '$.tracks**.title');
```

no dice. the problem here, of course, is that `WHERE IN` expects a list of values, not a json array. there's a type mismatch.

the solution is to use `MEMBER OF`.

this construct behaves kind of like a function and kind of like an operator. it takes it's left value and tests if it resides in the json array that is passed to it as an argument. 

```sql
SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist,
        JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.title')) as title
FROM    some_data
WHERE   "Queenie" MEMBER OF(CAST(JSON_EXTRACT(some_json, '$.tracks**.title') AS json));
+------------+---------------+
| artist     | title         |
+------------+---------------+
| Bratmobile | Kiss and Ride |
+------------+---------------+
```

the thing to note here is the argument to `MEMBER OF`. we have extracted all the `title` values from our json columns and mashed them together into one array. that 'array' isn't really an array, though; it's a string. and not even a json string, just a string string.

since `MEMBER OF` requires json-style data as an argument, we need to call `CAST` to turn the string of our array into an actual json array.

this solution is a bit messay, and if i am kludging up the wrong tree here and there is a better, more elegant way to go about this, i am very much open to input. however, this construct <i>does</i> work. 


## wait. this works on `TEXT` columns too?
the secret is that all of the stuff we've covered here also works on `TEXT` columns. if we run that `WHERE IN` select against our `some_text` column, it works fine:

```sql
SELECT  JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.artist')) as artist,
        JSON_UNQUOTE(JSON_EXTRACT(some_json, '$.title')) as title
FROM    some_data
WHERE   "Queenie" MEMBER OF(CAST(JSON_EXTRACT(some_text, '$.tracks**.title') AS json));
+------------+---------------+
| artist     | title         |
+------------+---------------+
| Bratmobile | Kiss and Ride |
+------------+---------------+
```

so why bother with the `JSON` column type at all? well, most importantly, when we use the `JSON` type we get automatic validation on insert or update. we can put invliad json into `TEXT` columns and mysql will never complain; it will just wait for us to be disappointed at select time.

secondly, the native `JSON` type is far more efficient. can we use extracted json values in a five table join? yes. will it be painfully slow if we're using `TEXT`? also yes. native `JSON` will be much faster.

finally, there will almost certainly be new json functions in future versions of mysql (including, hopefully, a more-sane way to do `WHERE IN`s) and there is no guarantee that they will behave well with `TEXT`. bottom line: if you know you're putting json in a column, make it a `JSON` column.

> 🔎  this post was originally written in the [grant horwood technical blog](https://gbh.fruitbat.io/2024/05/28/mysql-using-json-data-and-not-hating-it/)