SQL Select order by index of value in an array

Question

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?

Answer 1

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.