如何按照存储在另一个表中的 JSON 格式的 id 列表来选择行？

huangapple
go评论103阅读模式
英文:

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。我应该如何做？

  1. 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?

  1. 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
  1. 根据您未提供具体技术信息，基于标签，以下查询可用：
  3. SELECT * 
  4. FROM TABLE_B
  5. WHERE id IN (
  6.     SELECT id
  7.     FROM TABLE_A,
  8.          JSON_TABLE(
  9.              JSON_EXTRACT(json, '$.ids'),
  10.              '$[*]' COLUMNS (id INT PATH '$')
  11.          ) AS jt
  12.     WHERE TABLE_A.id = 10
  13. )
  • PostgreSQL
  1. 根据您未提供具体技术信息，基于标签，以下查询可用：
  3. SELECT * 
  4. FROM TABLE_B
  5. WHERE id IN (
  6.     SELECT json_array_elements_text((json->>'ids')::json)::integer
  7.     FROM TABLE_A
  8.     WHERE id = 10
  9. )
  • SQLite
  1. 根据您未提供具体技术信息，基于标签，以下查询可用：
  3. SELECT * 
  4. FROM TABLE_B
  5. WHERE id IN (
  6.     SELECT value
  7.     FROM TABLE_A, json_each(TABLE_A.json, '$.ids')
  8.     WHERE TABLE_A.id = 10
  9. )
英文:

since you didn't mention the specific technology that you are using so based on the tags, the below queries can be used

  • MySQL
  1. SELECT * 
  2. FROM TABLE_B
  3. WHERE id IN (
  4.     SELECT id
  5.     FROM TABLE_A,
  6.          JSON_TABLE(
  7.              JSON_EXTRACT(json, '$.ids'),
  8.              '$[*]' COLUMNS (id INT PATH '$')
  9.          ) AS jt
  10.     WHERE TABLE_A.id = 10
  11. )
  • PostgreSQL
  1. SELECT * 
  2. FROM TABLE_B
  3. WHERE id IN (
  4.     SELECT json_array_elements_text((json->'ids')::json)::integer
  5.     FROM TABLE_A
  6.     WHERE id = 10
  7. )
  • SQLite
  1. SELECT * 
  2. FROM TABLE_B
  3. WHERE id IN (
  4.     SELECT value
  5.     FROM TABLE_A, json_each(TABLE_A.json, '$.ids')
  6.     WHERE TABLE_A.id = 10
  7. )

huangapple
  • 本文由 发表于 2023年7月23日 16:39:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/76747325.html
  • mysql
  • postgresql
  • sql
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定