用于将数据插入节点 3 级深度的 mySQL JSON 文档存储方法 [英] mySQL JSON Document Store method for inserting data into node 3 levels deep

问题描述

我想从这里获取数据:

我在网上看到的所有示例似乎都迫使我将整个 JSON 读出到一个变量中，进行添加，然后将其塞回到 json_data 列中，这似乎很昂贵，尤其是对于大型 JSON 数据集.

有没有一个简单的方法我可以说

"INSERT INTO ssp_models JSON_INSERT <以某种方式深入到 'system-security-plan'.metadata.parties (name, type) VALUES ('Raytheon', 'organization') WHERE uuid = '66c2a1c8-5830-48bd-8fdd-55a1c3a52888'

我正在查看另一个用于插入 JSON 的 stackoverflow 示例:

如何创建和插入 JSON对象使用 MySQL 查询?

但是，当您从头开始时，这基本上很有用，而不是需要将 JSON 数据添加到已经存在的数据中.

解决方案

您可能需要阅读 https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html 并探索每个功能，并尝试它们如果您要继续在 MySQL 中处理 JSON 数据，请一一介绍.

我能够以这种方式完成您所描述的:

update ssp_models set json_data = json_array_append(json_data,'$.'system-security-plan'.metadata.party',json_object('name', 'Bingo', 'type', 'farmer'))其中 uuid = '66c2a1c8-5830-48bd-8fdd-55a1c3a52888';

然后我查了资料:

mysql>从 ssp_models\G 中选择 uuid, json_pretty(json_data)**************************** 1. 行 ***************************uuid:66c2a1c8-5830-48bd-8fdd-55a1c3a52888json_pretty(json_data):{系统安全计划":{uuid":66c2a1c8-5830-48bd-8fdd-55a1c3a52888"，元数据":{角色":[{id":法律官员"，职称":法务官"}],《标题》:《企业日志和审计系统安全计划》，当事人":[{名称":企业资产所有者"，类型":组织"，uuid":3b2a5599-cc37-403f-ae36-5708fa804b27"},{名称":企业资产管理员"，类型":组织"，uuid":833ac398-5c9a-4e6b-acba-2a9c11399da0"},{名称":宾果"，类型":农民"}]}}}

我从像你这样的数据开始，但在这个测试中，我截断了 party 数组之后的所有内容.

I want to take the data from here: https://raw.githubusercontent.com/usnistgov/oscal-content/master/examples/ssp/json/ssp-example.json

which I've pulled into a mySQL database called "ssp_models" into a JSON column called 'json_data', and I need add a new 'name' and 'type' entry into the 'parties' node with a new uuid in the same format as the example.

So in my mySQL database table, "ssp_models", I have this entry: Noting that I should be able to write the data by somehow referencing "66c2a1c8-5830-48bd-8fdd-55a1c3a52888" as the record to modify.

All the example I've seen online seem to force me to read out the entire JSON into a variable, make the addition, and then cram it back into the json_data column, which seems costly, especially with large JSON data-sets.

Isn't there a simple way I can say

"INSERT INTO ssp_models JSON_INSERT <somehow burrow down to 'system-security-plan'.metadata.parties (name, type) VALUES ('Raytheon', 'organization') WHERE uuid = '66c2a1c8-5830-48bd-8fdd-55a1c3a52888'

I was looking at this other stackoverflow example for inserting into JSON:

How to create and insert a JSON object using MySQL queries?

However, that's basically useful when you are starting from scratch, vs. needing to add JSON data to data that already exists.

解决方案

You may want to read https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html and explore each of the functions, and try them out one by one, if you're going to continue working with JSON data in MySQL.

I was able to do what you describe this way:

update ssp_models set json_data = json_array_append(
    json_data, 
    '$."system-security-plan".metadata.parties', 
    json_object('name', 'Bingo', 'type', 'farmer')
)
where uuid = '66c2a1c8-5830-48bd-8fdd-55a1c3a52888';

Then I checked the data:

mysql> select uuid, json_pretty(json_data) from ssp_models\G
*************************** 1. row ***************************
                  uuid: 66c2a1c8-5830-48bd-8fdd-55a1c3a52888
json_pretty(json_data): {
  "system-security-plan": {
    "uuid": "66c2a1c8-5830-48bd-8fdd-55a1c3a52888",
    "metadata": {
      "roles": [
        {
          "id": "legal-officer",
          "title": "Legal Officer"
        }
      ],
      "title": "Enterprise Logging and Auditing System Security Plan",
      "parties": [
        {
          "name": "Enterprise Asset Owners",
          "type": "organization",
          "uuid": "3b2a5599-cc37-403f-ae36-5708fa804b27"
        },
        {
          "name": "Enterprise Asset Administrators",
          "type": "organization",
          "uuid": "833ac398-5c9a-4e6b-acba-2a9c11399da0"
        },
        {
          "name": "Bingo",
          "type": "farmer"
        }
      ]
    }
  }
}

I started with data like yours, but for this test, I truncated everything after the parties array.

这篇关于用于将数据插入节点 3 级深度的 mySQL JSON 文档存储方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！