mySQL JSON Document Store method for inserting data into node 3 levels deep

Question

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.

Provide ssp_models's CREATE TABLE, some sample data as INSERT INTO (2-3 rows), the value to be inserted (present - but format it as code) and desired data state. And specify precise MySQL version. — Akina
– Akina, Commented Feb 20, 2021 at 5:34

Bill Karwin · Accepted Answer · 2021-02-20 22:58:39Z

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.

Collectives™ on Stack Overflow

mySQL JSON Document Store method for inserting data into node 3 levels deep

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related