I have one array of strings [“text1”, “text2”, “text3”] which happens to be saved as JSON array field on TableA

uniqueID	strings
1	["text1","text2","text3"]

And a TableB with a column that will contain the value of one of those strings

UNIQUEID	STRING	some other columns
1	“text3”	value
2	“text1”	value
3	“text2”	value
4	“text1”	value

I want to select from the table with results ordered by STRING_VALUE but in the order in which they appear in the array

something like

    SET @v1 := (SELECT strings FROM tableA where uniqueID = 1);
    SELECT UNIQUEID from TABLEB
    Where 1=1
    Order by  array_index in @v1?

So that the result would be all the "text1" followed by "text2" etc
(not because of alpahbetical order but because that is the order in which they appear in the array in tableA)

UNIQUEID
2
4
3
1

Is that possible?

CodePudding user response：

You can:

• join the two tables, by associating the json field to every row,
• use the JSON_SEARCH function to extract the string index where the "tableB.STRING" is located
• transform the string index into an unsigned integer index using the REPLACE function followed by CAST
• use this index inside the ORDER BY clause:

SELECT tableB.UNIQUEID
FROM tableB
INNER JOIN tableA ON 1=1
ORDER BY CAST(REPLACE(REPLACE(
              JSON_SEARCH(tableA.strings, 'one', tableB.STRING),
              '"$[', ''), ']"', ''
         ) AS UNSIGNED),
         tableB.UNIQUEID

Check the demo here.

CodePudding user response：

use JSON_TABLE.

SELECT b.UNIQUEID
FROM 
    tableB b
    JOIN 
        (
        SELECT * 
        FROM 
            JSON_TABLE
            (
                (SELECT STRS FROM tableA WHERE UNIQUEID = 1), '$[*]' 
                COLUMNS(
                SEQ FOR ordinality, 
                STR varchar(10) path '$')
            ) rs
        
        ) jt ON (b.STR = jt.STR)
ORDER BY jt.SEQ
;

table col definitionS.

tableA

UNIQUEID	STRS

tableB

UNIQUEID	STR	OTHERS

(tested on mariadb 10.6.5)