英文:
How to select rows by a list of ids in json format which stored in another table?
问题
我有一个表（TABLE_A），其中有一个包含JSON值的列，该JSON中包含来自另一个表的id列表等一些数据。
该表的简化版本如下所示：
| id | json |
|---|---|
| 10 | {"ids": [1, 2, 3, 4, 5]} |
现在我想从另一个表(TABLE_B)中选择这些id。我应该如何做？
SELECT * FROM TABLE_B WHERE `id` IN (SELECT TABLE_A.json.ids FROM TABLE_A WHERE `id` = 10)
我需要子查询方法（如上述伪代码）和联接方法，但我不知道该怎么做。
英文:
I have a table (TABLE_A) which has a column of json value, some data is in this json, including a list of ids from another table.
A simplified version of this table looks like this:
| id | json |
|---|---|
| 10 | {"ids": [1,2,3,4,5]} |
Now I want to select those ids from another table (TABLE_B). How can I do that?
SELECT * FROM TABLE_B WHERE `id` IN (SELECT TABLE_A.json.ids FROM TABLE_A WHERE `id` = 10)
I need both subqueries approach (like above pseudocode) and also join approach, but I have no idea what should I do.
答案1
得分: 0
- MySQL
根据您未提供具体技术信息，基于标签，以下查询可用：
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT id
FROM TABLE_A,
JSON_TABLE(
JSON_EXTRACT(json, '$.ids'),
'$[*]' COLUMNS (id INT PATH '$')
) AS jt
WHERE TABLE_A.id = 10
)
- PostgreSQL
根据您未提供具体技术信息，基于标签，以下查询可用：
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT json_array_elements_text((json->>'ids')::json)::integer
FROM TABLE_A
WHERE id = 10
)
- SQLite
根据您未提供具体技术信息，基于标签，以下查询可用：
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT value
FROM TABLE_A, json_each(TABLE_A.json, '$.ids')
WHERE TABLE_A.id = 10
)
英文:
since you didn't mention the specific technology that you are using so based on the tags, the below queries can be used
- MySQL
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT id
FROM TABLE_A,
JSON_TABLE(
JSON_EXTRACT(json, '$.ids'),
'$[*]' COLUMNS (id INT PATH '$')
) AS jt
WHERE TABLE_A.id = 10
)
- PostgreSQL
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT json_array_elements_text((json->'ids')::json)::integer
FROM TABLE_A
WHERE id = 10
)
- SQLite
SELECT *
FROM TABLE_B
WHERE id IN (
SELECT value
FROM TABLE_A, json_each(TABLE_A.json, '$.ids')
WHERE TABLE_A.id = 10
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论