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

Question

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?

Bill Karwin · Accepted Answer · 2022-11-11 17:01:42Z

0

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.

answered Nov 11, 2022 at 17:01

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

thank you so much! I had not appreciated the differences between JSON_EXTRACT and JSON_VALUE

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related