MySQL, JSON, indexing and generated columns

For quite some time now I wanted to play around with the recently added JSON type in MySQL.  Finally, I have a project where MySQL version is high enough to support it, and the requirements are such that this choice makes sense.

The first impression was great – JSON type is basically LONGTEXT type with a bunch of added functionality to manipulate JSON data.  It took no time to setup tables and necessary queries to work with it.

The second iteration though raised a few questions.  Large tables, with complex JSON structures were rather slow in some of the more complex queries.  The first solution to look at was obviously indexes.  Turns out, MySQL does not support indexing of the JSON fields. Bummer.

But there is a rather elegant work around.  It involves another recently added feature, of which I haven’t heard about until today – GENERATED columns.  Think of table views, but on the column level, not table level.  And generated columns can be indexed.

In fact, there’s a whole lot that you can do with GENERATED columns in general, and JSON data in particular.  This blog post – “MySQL for JSON: Generated Columns and Indexing” – provides a great starting point with examples and explanations, including a scenario with the primary key of the table being a generated column, with the data from the JSON-typed column.

Awesomeness!

Headless CMS: REST vs JSON:API vs GraphQL

Headless CMS: REST vs JSON:API vs GraphQL” is an interesting comparison of the REST, JSON:API, and GraphQL:

In this blog post, we will compare REST, JSON:API and GraphQL. First, we’ll look at an architectural, CMS-agnostic comparison, followed by evaluating some Drupal-specific implementation details.

fx – command-line tool and terminal JSON viewer

fx is yet another command-line tool for working with JSON data. Some of the supported features:

  • Formatting and highlighting
  • Standalone binary
  • Interactive mode 
  • Themes support

This looks like a good alternative or a complimenting tool for both jq and jo.

jq: deep comparison of 5GB files

I’ve blogged about jq – a lightweight and flexible command line JSON processor – a few times already (look here and here).  Today I came across this blog post that showcases jq in deep comparison of really large JSON files (5 GB or so).  This is not something that I need on a daily basis, but I’m sure it’ll come in handy one day.

php-jsonq – a simple, elegant PHP package to query over any type of JSON data


php-jsonq provides an easy, yet powerful way to build queries for any JSON data (or PHP data structures for that matter, which are a step away).  This has a variety of useful applications – data migration, API response filtering, complex configurations manipulation, and so on, and so forth.