Execute > 1 SQL statement in single prepared statement

Question

Alright so I'll try to explain it as simple as possible; consider that I have two database tables (MySQL Server / MariaDB, database-related tasks coded in procedural style in PHP using prepared statements):

1. in one, I have a column of datatype JSON, whose content corresponds to sth like {name1:info,name2:info}

2. In another one, I have simple non-json records, having a structure like:

name  | status
------+--------
name1 | statusX
------+--------
name2 | statusY

My Goal: I need to retrieve the name2 from table 1), but I also need to retrieve the status of the person having that same name (which in this case is statusY). Note that, for the retrieval of name2, I cannot rely on indexes of the json object (name2 may be the first key of the json object).

How I would do it so far: A) Get the name2 from table 1) in a first query, sanitize it, and B) use it in the second query which then correctly retrieves the statusY

Both statements A) and B) are parametrized prepared sql statements, triggered by an AJAX Call at a regular interval (AJAX Polling).

Given that these database queries are thus executed frequently, I want them to be executed as fast as possible, and thus ideally reduce my two queries above to a single one. My problem: I need the result of statement A) to execute statement B), so I cannot summarize the two queries into a single prepared statement, as prepared statements cannot contain multiple sql statements. The best solution to reach what I want is create a stored procedure like:

SET @name = SELECT ..... FROM table_1; SELECT .... FROM table_2;

And then execute it as parametrized prepared statement; is that correct? I'm not at all experienced with stored procedures in MySQL Server, didn't really need them yet, but they seem to be the only solution if you want to wrap > 1 sql statements into a single prepared statement. Is this assumption, and thus the conclusion that I gotta create a stored procedure to reach what I want, correct?

IMPORTANT NOTE: I don't know the name I need to query. From the two names of the json column of table 1), I only know the other name. In other words, I have one name of a person X, and I want to get the status of all the persons which have been associated with that person X in table 1), while the status of each person is listed in table 2), to avoid to have duplicate status storage in the DB. ATM, I retrieve the other names of each relation record from DB 1) by using a conditional statement saying sth like

UPDATE

See added answer below, got it working.

Answer 1

You can query JSON data type with MySQL (if version > 5.7), and thus you can simply do everything with a single query

Give this a try

SELECT t1.name1, t1.name2, t2.status
FROM
(
  SELECT  JSON_EXTRACT(your_json_column, "$.name1") AS name1,
        JSON_EXTRACT(your_json_column, "$.name2") AS name2
  FROM table1
  WHERE JSON_EXTRACT(your_json_column, "$.name1") = 'info'
) t1
INNER JOIN table2 t2 ON t2.`name`=t1.name2

Adapt the name of your_json_column. Also I assumed that you wanted to search the name2 of a specific name1, thus my WHERE clause, remove it if it was a false assumption.

Answer 2

Okay got it working, pretty much thanks to the solution proposed by Thomas G and some hints of JNevill (cheers guys!):

SELECT t1.info1, t1.info2, t1.info3, t1.other_name, t2.status FROM (
    SELECT
    field1 AS info1,
    field2 AS info2,
    field3 AS info3,
    CASE
    WHEN JSON_VALUE(JSON_KEYS(json_names_column),"$[0]") = 'name1'
    THEN JSON_VALUE(JSON_KEYS(json_names_column),"$[1]")
    ELSE JSON_VALUE(JSON_KEYS(json_names_column),"$[0]")
    END
    AS other_name
    FROM table1
    WHERE id = 345
) t1 INNER JOIN table2 t2 ON t1.other_name = t2.name;

Note that I used JSON_VALUE(JSON_KEYS()) instead of JSON_EXTRACT, to only return the needed name as name data of t1, and because I don't know the name to retrieve before the query, so I cannot use the WHEREclause proposed by Thomas G.