Retrieve property values from json array filtered on another property value

Question

I have a table with a column (column_metadata) that holds a json array

[{
    "ORDER": -1,
    "READONLY": false,
    "COLUMNNAME": "id",
    "COLUMNTYPE": "int",
    "FILTERABLE": false,
    "SUMMARYVIEW": false,
    "WHITELISTED": false
  },
  {
    "ORDER": -1,
    "READONLY": true,
    "COLUMNNAME": "name",
    "COLUMNTYPE": "varchar",
    "FILTERABLE": true,
    "SUMMARYVIEW": true,
    "WHITELISTED": true
  },
  {
    "ORDER": -1,
    "READONLY": false,
    "COLUMNNAME": "description",
    "COLUMNTYPE": "varchar",
    "FILTERABLE": true,
    "SUMMARYVIEW": true,
    "WHITELISTED": true
  }]

I am trying to get all COLUMNNAME list for where WHITELISTED is true. I got select json_extract(column_metadata, '$[*].COLUMNNAME') from myTable that returns me all the column names returned separated by comma.

However, when I add a where clause

select json_extract(column_metadata, '$[*].COLUMNNAME') from myTable
WHERE json_extract(column_metadata, '$[*].WHITELISTED') = true;

it doesn't return anything. How do we construct the where clause with json_extract?

EDIT: I am using MySQL 5.7 so JSON_TABLE() isn't available

Bill Karwin · Answer 1 · 2020-10-27T04:26:43.763

You can't do this with JSON_EXTRACT(). In general, JSON_EXTRACT() doesn't work well to search fields of arrays of JSON objects. You can give JSON_EXTRACT() a path, you can't use JSON_EXTRACT() to generate an array result in a scalar context, which is what you show in your hypothetical (non-working) SQL query.

What you need is JSON_TABLE(), which was introduced in MySQL 8.0:

SELECT j.COLUMNNAME
FROM mytable, JSON_TABLE(mytable.column_metadata, '$[*]' COLUMNS(
  `ORDER` SMALLINT PATH '$.ORDER',
  READONLY BOOL PATH '$.READONLY',
  COLUMNNAME VARCHAR(64) PATH '$.COLUMNNAME',
  COLUMNTYPE VARCHAR(20) PATH '$.COLUMNTYPE',
  FILTERABLE BOOL PATH '$.FILTERABLE',
  SUMMARYVIEW BOOL PATH '$.SUMMARYVIEW',
  WHITELISTED BOOL PATH '$.WHITELISTED'
)) AS j
WHERE j.WHITELISTED = true

Result given your example data:

+-------------+
| COLUMNNAME  |
+-------------+
| name        |
| description |
+-------------+

In my opinion, JSON_TABLE() is a PITA. You have to write out that verbose, complex syntax every time you want to search for a specific value in a specific field in JSON data.

It would be far easier to use normal tables and columns instead of JSON.

CREATE TABLE column_metadata (
  `ORDER` SMALLINT,
  READONLY BOOL,
  COLUMNNAME VARCHAR(64),
  COLUMNTYPE VARCHAR(20),
  FILTERABLE BOOL,
  SUMMARYVIEW BOOL,
  WHITELISTED BOOL
);

Then your query is a lot easier to develop and easier to read:

SELECT COLUMNNAME FROM column_metadata WHERE WHITELISTED = true;

You can also optimize queries by defining indexes in the normal table, whereas making indexes on JSON arrays is confusing and requires even more new features introduced in MySQL 8.0.17.

I am using MySQL 5.7. Doesn't have the JSON_TABLE(). Nor do I have the option to split the Json out into a table as it is a column in a legacy table. Looking for a way to do it given my constraints — lohiarahul, Oct 27 '20 at 05:43
Sorry, I can't help. JSON is a bad idea in a relational database. — Bill Karwin, Oct 27 '20 at 06:47

Retrieve property values from json array filtered on another property value

1 Answers1