How to replace keys of a JSON values in a column using the values of another column in a different table?

Question

I have two tables in mysql schema, let's call them sites and fields. The sites table as the following schema:

create table if not exists sites
(
    id int auto_increment
        primary key,
    collection_id int null,
    name varchar(255) null,
    properties text null,
)
;

And the fields has the following schema

create table fields
(
    id int auto_increment
        primary key,
    collection_id int null, 
    name varchar(255) null,
    code varchar(255) null,
)
;

The two tables can be joined on the collection_id column. The sites table stores json data in the properties column with the keys of the json objects being values of id column in field table. For example, here is a sample json that can be found in the properties column

{"1281":"Type A","1277":4}

The keys in the json above are the ids of a record in the fields.

+---------+--------------+--------------+
|id       |  name        |   code       |
+---------------------------------------+
| 1277    |  Years       |  Yr          |
+---------------------------------------+
| 1281    | Type         |  Ty          |
+---------+--------------+--------------+

Now, I want to output properties json where the keys are replaced by the names of the field instead of the id values. Using the above example the output should look like:

{"Type": "Type A", "Years": 4}

I have tried the following so far

select JSON_OBJECT(fd.name, JSON_EXTRACT(st.properties, concat('$."', fd.id, '"'))) as prop  
from sites as st join fields as fd on fd.collection_id = st.collection_id where st.collection_id = 145 and

JSON_EXTRACT(st.properties, concat('$."', fd.id, '"')) is not  null ;

However, this produces json object for each field instead of site.

It outputs something like below:

 +----------------------------+
 |        prop                |
+-----------------------------+
 |  {"Type": "Type A"}        |
 |                            |
 +----------------------------+
 |   {"Type": "Type B"}       |
 |                            |
 +-----------------------------+
 |                            |
 |    {"Year": 4}             |
 |                            |
 +----------------------------+

How can modify the above code to get desired output? Or Is there a better solution?

Generally, there is not a good reason to store complex fields in a JSON. There are exceptions, but this in generally an anti-pattern. Can you not break this out into fields so this is a standard query? If you must store this as JSON, perhaps the JSON datatype will serve you better. — alwayslearning, Apr 01 '19 at 14:42

xabush · Accepted Answer · 2019-04-10T13:12:28.637

I arrived at the solution using the group_concat function to concatenate the results for each site into one after I have grouped them by site id. Here is the query:

select concat('{' ,group_concat(concat('\"', cast(fd.code as char(50)), '\":' , JSON_EXTRACT(st.properties, concat('$.\"', fd.id, '\"')))), '}') as prop , st.id as site 
from sites as st join fields as fd on fd.collection_id = st.collection_id 
where st.collection_id = 145 and
JSON_EXTRACT(st.properties, concat('$."', fd.id, '"')) is not  null
group by st.id

Note: This solution assumes you are on MySQL version 5.7+ as that's when JSON_EXTRACT function was introduced. If you are on a lower version, use the UDF in this answer as a replacement for JSON_EXTRACT.

How to replace keys of a JSON values in a column using the values of another column in a different table?

1 Answers1