Extract SELECT expression using MySQL function or procedure

Question

I'm interested in simplifying an SQL query/view by extracting the select expression (select_expr from the MySQL docs). Each of the select_expr is essentially duplicated with a small amount of variation that could be extracted into variables.

For example, here is an existing query/view.

CREATE OR REPLACE VIEW my_view AS
SELECT

  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_1', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_1,

  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_2', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_2,

  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_3', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_3,

FROM my_table mt;

The variable bits are: field_1, field_2, and field_3.

In theory, this is what I would like to do:

CREATE OR REPLACE VIEW my_view AS
SELECT

  get_select_expr('field_1') AS field_1,
  get_select_expr('field_2') AS field_2,
  get_select_expr('field_3') AS field_3,

FROM my_table mt;

I've been trying something like the following, but not sure how to get the select_expr to evaluate. It makes sense that it's returning a string, but I can't figure out how to get it to evaluate. Maybe I should be using a procedure, but this is where my MySQL knowledge breaks down.

DROP FUNCTION IF EXISTS get_select_expr;
CREATE FUNCTION get_select_expr (field_name VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN concat('json_unquote(json_extract(mt.response, concat(
    SUBSTRING_INDEX(json_unquote(JSON_SEARCH(mt.response, 
    \'one\', \'pref.', field_3, '', NULL, \'$.f[*].q\')), 
    \'.\', 2), \'.\', \'value\')))');

SELECT get_select_expr('field_1') AS field_1 FROM my_table;

I've gone through all of the suggested similar questions, but not finding what I need. Any idea where I may be going wrong, or pointers? I'm not even sure I'm searching for the right terms.

This is not going to work, you need to have the full sql statement in one piece. Contents of a string variable remain data. You can use prepared statements to dynamically assemble and execute sql statements, but still they need the full statement with the from clause. — Shadow, May 23 '17 at 17:51
What you can do is to encapsulate the formula in a function and pass the field names as a parameter to calculate the output. — Shadow, May 23 '17 at 17:55

score 2 · Accepted Answer · answered May 24 '17 at 00:45

You are over complicating the code, there is no need to dynamically generate sql code here and it will not work anyway.

Just create a function that takes a field value and a json field value as parameter and you do not need dynamic sql:

DROP FUNCTION IF EXISTS get_select_expr;
CREATE FUNCTION get_select_expr (field_name VARCHAR(255), json_field_name varchar (255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN json_unquote(json_extract(field_name, concat(
    SUBSTRING_INDEX(json_unquote(JSON_SEARCH(field_name, 
    'one', 'pref.', json_field_name, '', NULL, '$.f[*].q')), 
    '.', 2), '.', 'value')));

SELECT get_select_expr(my_table.response, 'field_1') AS field_1 FROM my_table;

I see, if I remove `concat` from my question it works as expected. I realized I had asked the question with the aliases in place, but was hoping to not have to duplicate the field name. Before I saw your answer I had created functions that generated each SQL part, and then a final function that put them all together into a complete SQL query. Then used a procedure to execute it. It was a bit too hard to follow. After discussing with colleagues, we're okay with a bit of duplication and going to use this method. Thanks — wsams, May 24 '17 at 17:31

Extract SELECT expression using MySQL function or procedure

1 Answers1