SQL选择订单按数组中的值索引

发布于 2025-02-07 06:48:58 字数 1111 浏览 4 评论 0原文

我有一个字符串[“ text1”，“ text2”，“ text3”]，恰好被保存为json阵列字段tablea

simelidid	字符串
1	[“ text1”，“ text2”，“ text2”，“ text3”]

和带有带有的tableb一个将包含其中一个字符串的值的列

simolorId	字符串	其他列
1	“ text3”	值
2	“ text1”	值
3	“ text2”	值
4	“ text1”	值

我想从表中选择，并由String_value订购的结果，但是按照它们出现在数组中的顺序，

以

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

使结果将是所有“ text1”，然后是“ text2”等顺序）

而是
因为
（不是
中的阵列中的
因为那是它们出现在tablea

Alpahbotical顺序，

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑着哭最痛 2025-02-14 06:48:58

您可以：

来加入两个表格。
通过将JSON字段与每个行关联，使用 json_search 函数提取字符串索引，其中“ tableb.string ” iS
jes使用替换函数的未签名整数索引，然后是cast
在条款中使用订单中的此索引：

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

检查demo 在这里。

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.

回复收藏 0 原文

記柔刀 2025-02-14 06:48:58

使用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 '
表COL定义。
 tablea



唯一的
strs









 tableb



 siless
 str
其他










（在Mariadb 10.6.5上测试）
)
            ) rs
        
        ) jt ON (b.STR = jt.STR)
ORDER BY jt.SEQ
;

表COL定义。

tablea

唯一的	strs

tableb

siless	str	其他

（在Mariadb 10.6.5上测试）

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 '
table col definitionS.
tableA



UNIQUEID
STRS









tableB



UNIQUEID
STR
OTHERS










(tested on mariadb 10.6.5)
)
            ) rs
        
        ) jt ON (b.STR = jt.STR)
ORDER BY jt.SEQ
;