Search code examples
mysqlsqlmysql-routinesmysql-function

Extract SELECT expression using MySQL function or procedure

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.

Solution

  • 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;