从SQL的数据库中的数组中的对象获取数据

发布于 2025-02-07 12:14:16 字数 2216 浏览 2 评论 0 原文

我有一个表（主题）中的一个表（主题），我的数组中有内部的对象（数据库中的每一行中的这个数组都不同）：

第1行中的colum colum：

[
    {
        "topicId": "CON",
        "Dcentros": [
            {
                "dcentro_id": "9388TG",
                "dcentro_name": "9388TG"
            },
            {
                "dcentro_id": "9387OC",
                "dcentro_name": "9387OC"
            }
        ],
        "topicName": "CONFLUENCE"
    },
    {
        "topicId": "PMO",
        "Dcentros": [
            {
                "dcentro_id": "9387OC",
                "dcentro_name": "9387OC"
            }
        ],
        "topicName": "PMO"
    },
    {
        "topicId": "TSM",
        "Dcentros": [
            {
                "dcentro_id": "9387CC",
                "dcentro_name": "9387CC"
            }
        ],
        "topicName": "TSM"
    }
]

row2中的主题列：

[
        {
            "topicId": "COS",
            "Dcentros": [
                {
                    "dcentro_id": "9388TG",
                    "dcentro_name": "9388TG"
                },
                {
                    "dcentro_id": "9387OC",
                    "dcentro_name": "9387OC"
                }
            ],
            "topicName": "CONNECTIONS"
        },
        {
            "topicId": "PTO",
            "Dcentros": [
                {
                    "dcentro_id": "9387OC",
                    "dcentro_name": "9387OC"
                }
            ],
            "topicName": "PTO"
        },
        {
            "topicId": "TAM",
            "Dcentros": [
                {
                    "dcentro_id": "9387CC",
                    "dcentro_name": "9387CC"
                }
            ],
            "topicName": "TAMIT"
        }
    ]

我想提取如果'dcentro_id'存在不同对象内部的'dcentro_id'，则数组内部的每个对象的“主题名”。

PE：（提取“ dcentro_id：9387cc”的所有“ topicname”） 结果应该是：'tamit'，'tsm'。

我确实在MySQL中尝试了此操作：

SELECT  JSON_EXTRACT(topics, '$[*].topicName') as c_name  FROM tool_bbdd.workspace WHERE JSON_EXTRACT(JSON_EXTRACT(topics, '$[*].Dcentros[*].dcentro_id'), '$[*]') = '9387CC';

但是不正常，请提取对象中的所有主题，而不仅仅是它存在的主题。

一些帮助。谢谢

原文

i have a table where in a column (topics) i have this kind of array with objects inside (this array with objects is different for each row in the database):

topics colum in row1:

[
    {
        "topicId": "CON",
        "Dcentros": [
            {
                "dcentro_id": "9388TG",
                "dcentro_name": "9388TG"
            },
            {
                "dcentro_id": "9387OC",
                "dcentro_name": "9387OC"
            }
        ],
        "topicName": "CONFLUENCE"
    },
    {
        "topicId": "PMO",
        "Dcentros": [
            {
                "dcentro_id": "9387OC",
                "dcentro_name": "9387OC"
            }
        ],
        "topicName": "PMO"
    },
    {
        "topicId": "TSM",
        "Dcentros": [
            {
                "dcentro_id": "9387CC",
                "dcentro_name": "9387CC"
            }
        ],
        "topicName": "TSM"
    }
]

topics column in row2:

[
        {
            "topicId": "COS",
            "Dcentros": [
                {
                    "dcentro_id": "9388TG",
                    "dcentro_name": "9388TG"
                },
                {
                    "dcentro_id": "9387OC",
                    "dcentro_name": "9387OC"
                }
            ],
            "topicName": "CONNECTIONS"
        },
        {
            "topicId": "PTO",
            "Dcentros": [
                {
                    "dcentro_id": "9387OC",
                    "dcentro_name": "9387OC"
                }
            ],
            "topicName": "PTO"
        },
        {
            "topicId": "TAM",
            "Dcentros": [
                {
                    "dcentro_id": "9387CC",
                    "dcentro_name": "9387CC"
                }
            ],
            "topicName": "TAMIT"
        }
    ]

I would like to extract the "topicName" for each object inside the array from the different rows, if 'dcentro_id' exist inside the differents objects.

p.e:
(extract all the 'topicName' for the 'dcentro_id:9387CC')
the result should be, and array with: 'TAMIT', 'TSM'.

I did try this in MySQL:

SELECT  JSON_EXTRACT(topics, '$[*].topicName') as c_name  FROM tool_bbdd.workspace WHERE JSON_EXTRACT(JSON_EXTRACT(topics, '$[*].Dcentros[*].dcentro_id'), '$[*]') = '9387CC';

But not works fine, extract all the topics in the object, not just the topic where it exist.

Some help.
thanks

分享到QQ

分享到微博

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

发布评论

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

掩耳倾听 2025-02-14 12:14:16

目前尚不清楚您想要什么结果，但这只会选择具有特定 dcentro_id 的元素。

SELECT
  t.*
FROM
  tool_bbdd.workspace
CROSS JOIN
  JSON_TABLE(
    topics,
    '$[*]' COLUMNS (
      topicRowID FOR ORDINALITY,
      topicId   VARCHAR(32) PATH '$.topicId',
      topicName VARCHAR(32) PATH '$.topicName',
      NESTED PATH '$.Dcentros[*]' COLUMNS (
        dcentroRowID FOR ORDINALITY,
        dcentro_id   VARCHAR(32) PATH '$.dcentro_id',
        dcentro_name VARCHAR(32) PATH '$.dcentro_name'
      )
    )
  )
    AS t
WHERE
  t.dcentro_id = '9387OC'

主题Rowid	topipID	主题名称	dcentRorowId	dcentro_id	dcentro_name
1	con	Confluence	2	9387oc	9387oc
2	pmo PMO	PMO	1	9387oc 9387oc	9387oc

使用Mariadb作为MySQL，因为MySQL似乎在网站上打破了网站...

https：//dbfiddle.uk/？rdbms = mariadbms = mariadbms = mariadbms = mariadbms >

It's not clear what results you want, but this will pick out only the elements with a specific dcentro_id.

SELECT
  t.*
FROM
  tool_bbdd.workspace
CROSS JOIN
  JSON_TABLE(
    topics,
    '$[*]' COLUMNS (
      topicRowID FOR ORDINALITY,
      topicId   VARCHAR(32) PATH '$.topicId',
      topicName VARCHAR(32) PATH '$.topicName',
      NESTED PATH '$.Dcentros[*]' COLUMNS (
        dcentroRowID FOR ORDINALITY,
        dcentro_id   VARCHAR(32) PATH '$.dcentro_id',
        dcentro_name VARCHAR(32) PATH '$.dcentro_name'
      )
    )
  )
    AS t
WHERE
  t.dcentro_id = '9387OC'