Search code examples
mysqlmysql-json

How to populate a JSON path dynamically in MySQL using only SQL syntax

I'm trying to write a SELECT statement that dynamically populates a JSON path based on values from a metadata table.

I have two tables: a table of metadata describing the JSON structure, and a table of business data including each employee's name and a JSON with the described properties:

CREATE TABLE TempMetaData( Sequence INT, CustomFieldName VARCHAR(64) );
INSERT INTO TempMetaData VALUES (1,'FavoriteFruit'), (2,'FavoriteColor'), (3,'FavoriteAnimal');

CREATE TABLE TempBusinessData( EmployeeName VARCHAR(16), SpecialProperties JSON );
INSERT INTO TempBusinessData VALUES ('Ann', JSON_OBJECT('FavoriteFruit','Apple' ,'FavoriteColor','Red', 'FavoriteAnimal','Dog') ),
                                    ('Bob', JSON_OBJECT('FavoriteFruit','Orange','FavoriteColor','Blue','FavoriteAnimal','Cat') );

The following is a concrete, hard-coded example that works perfectly but doesn't meet my requirements:

SELECT EmployeeName, JSON_VALUE( SpecialProperties, '$.FavoriteFruit' ) FROM TempBusinessData;

Here is the concept of what I am trying to do:

SELECT EmployeeName, JSON_VALUE( SpecialProperties, (SELECT CONCAT('$.', (SELECT CustomFieldName FROM TempMetaData WHERE Sequence = 1) ) ) FROM TempBusinessData;

I'd appreciate any guidance on how I can replace the hard-coded path with a sub-SELECT that populates the path name from the TempMetaData table. Thanks for any help!

P.S. I found this post which seems to be related, but I didn't understand it: How to populate a json array object dynamically?

Solution 
  • SELECT EmployeeName, JSON_UNQUOTE(JSON_EXTRACT(SpecialProperties, 
  CONCAT('$.', (SELECT CustomFieldName FROM TempMetaData WHERE Sequence = 1)))) AS field1 
FROM TempBusinessData

    Result:

    +--------------+--------+
| EmployeeName | field1 |
+--------------+--------+
| Ann          | Apple  |
| Bob          | Orange |
+--------------+--------+

    You can use an expression for the path in JSON_EXTRACT(), but not in JSON_VALUE().

    https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value says:

    path is a JSON path pointing to a location in the document. This must be a string literal value.