JSON 구조와 검증

DROP TEMPORARY TABLE IF EXISTS json_intro;
CREATE TEMPORARY TABLE json_intro (
  post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  metadata JSON NOT NULL
);

INSERT INTO json_intro VALUES (
  1,
  JSON_OBJECT(
    'source', 'editorial',
    'license', 'MIT',
    'tags', JSON_ARRAY('mysql', 'index'),
    'review', JSON_OBJECT('score', 92, 'published', TRUE)
  )
);

SELECT
  metadata->'$.source' AS source_json,
  metadata->>'$.source' AS source_text,
  JSON_VALUE(metadata, '$.review.score' RETURNING UNSIGNED) AS review_score,
  JSON_EXTRACT(metadata, '$.tags[0]') AS first_tag_json,
  JSON_CONTAINS(
    JSON_EXTRACT(metadata, '$.tags'),
    JSON_QUOTE('mysql')
  ) AS has_mysql
FROM json_intro
WHERE post_id = 1;

source_json: "editorial"
source_text: editorial
review_score: 92
first_tag_json: "mysql"
has_mysql: 1

UPDATE json_intro
SET metadata = JSON_SET(
  metadata,
  '$.license', 'Apache-2.0',
  '$.reviewer', 'moderator-1'
)
WHERE post_id = 1;

UPDATE json_intro
SET metadata = JSON_INSERT(metadata, '$.source', 'ignored')
WHERE post_id = 1;

UPDATE json_intro
SET metadata = JSON_REPLACE(metadata, '$.review.score', 95)
WHERE post_id = 1;

UPDATE json_intro
SET metadata = JSON_REMOVE(metadata, '$.reviewer')
WHERE post_id = 1;

SELECT
  metadata->>'$.source' AS source_text,
  metadata->>'$.license' AS license_text,
  JSON_VALUE(metadata, '$.review.score' RETURNING UNSIGNED) AS review_score,
  JSON_CONTAINS_PATH(metadata, 'one', '$.reviewer') AS has_reviewer
FROM json_intro;

SELECT
  JSON_KEYS(metadata) AS root_keys,
  JSON_LENGTH(metadata, '$.tags') AS tag_count,
  JSON_TYPE(metadata->'$.review') AS review_type,
  JSON_VALID(metadata) AS syntax_valid,
  JSON_PRETTY(metadata) AS formatted_document
FROM json_intro;

DROP TABLE IF EXISTS post_metadata_bad;
CREATE TABLE post_metadata_bad(
 post_id BIGINT UNSIGNED PRIMARY KEY,metadata JSON NOT NULL
) ENGINE=InnoDB;
INSERT INTO post_metadata_bad VALUES
 (1,JSON_OBJECT('license',JSON_ARRAY('MIT','Apache'),'tags',JSON_ARRAY(10,20))),
 (2,JSON_OBJECT('source','provider-a','unexpected_secret','plain-text-token'));
SELECT post_id,JSON_TYPE(metadata->'$.license') license_type,
 JSON_TYPE(metadata->'$.tags[0]') tag_type,
 JSON_CONTAINS_PATH(metadata,'one','$.source') has_source
FROM post_metadata_bad;

post 1: license=ARRAY, first topic=INTEGER, source=0
post 2: license missing, unexpected_secret present
JSON syntax errors=0
business-valid documents=0

DROP PROCEDURE IF EXISTS patch_post_metadata;
DROP TABLE IF EXISTS post_metadata;
CREATE TABLE post_metadata(
 post_id BIGINT UNSIGNED PRIMARY KEY,
 schema_version SMALLINT UNSIGNED NOT NULL,
 metadata JSON NOT NULL,
 updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
 CONSTRAINT fk_post_metadata_post FOREIGN KEY(post_id)
  REFERENCES posts(id) ON DELETE CASCADE,
 CONSTRAINT chk_post_metadata_version CHECK(schema_version=1),
 CONSTRAINT chk_post_metadata_schema CHECK(JSON_SCHEMA_VALID(
  '{"type":"object","required":["source","license"],"additionalProperties":false,
    "properties":{"source":{"type":"string","minLength":1,"maxLength":80},
    "license":{"type":"string","minLength":1,"maxLength":60},
    "external_id":{"type":"string","maxLength":120},
    "tags":{"type":"array","maxItems":10,"items":{"type":"string","minLength":1,"maxLength":40}}}}',
  metadata
 ))
) ENGINE=InnoDB;

SET @post_id=(SELECT id FROM posts ORDER BY id LIMIT 1);
SET @other_post_id=(SELECT id FROM posts WHERE id<>@post_id ORDER BY id LIMIT 1);
INSERT INTO post_metadata(post_id,schema_version,metadata)
VALUES(@post_id,1,JSON_OBJECT(
 'source','editorial','license','CC-BY-4.0','external_id','post-701',
 'tags',JSON_ARRAY('mysql','index')));

DELIMITER //
CREATE PROCEDURE patch_post_metadata(
 IN p_post BIGINT UNSIGNED,IN p_license VARCHAR(60),IN p_tags JSON)
BEGIN
 DECLARE v_doc JSON; DECLARE v_next JSON;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
 START TRANSACTION;
 SELECT metadata INTO v_doc FROM post_metadata
 WHERE post_id=p_post FOR UPDATE;
 IF v_doc IS NULL OR JSON_TYPE(p_tags)<>'ARRAY' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='metadata or tags unavailable';
 END IF;
 SET v_next=JSON_SET(v_doc,'$.license',p_license,'$.tags',p_tags);
 UPDATE post_metadata SET metadata=v_next WHERE post_id=p_post;
 COMMIT;
END//
DELIMITER ;
CALL patch_post_metadata(
 @post_id,'CC-BY-SA-4.0',JSON_ARRAY('mysql','btree','query-plan'));
SELECT metadata FROM post_metadata WHERE post_id=@post_id;

schema version=1
source=editorial
license=CC-BY-SA-4.0
tags length=3
schema violations=0

INSERT INTO post_metadata(post_id,schema_version,metadata)
VALUES(@other_post_id,1,JSON_OBJECT('source','import-provider'));
-- ERROR 3819: required license missing

CALL patch_post_metadata(
 @post_id,'MIT',JSON_ARRAY('mysql',42));
-- ERROR 3819: tag item must be string

UPDATE post_metadata
SET metadata=JSON_SET(metadata,'$.unexpected_secret','token')
WHERE post_id=@post_id;
-- ERROR 3819: additionalProperties false

SELECT post_id FROM post_metadata
WHERE NOT JSON_SCHEMA_VALID(
 '{"type":"object","required":["source","license"],"additionalProperties":false,
   "properties":{"source":{"type":"string","minLength":1,"maxLength":80},
   "license":{"type":"string","minLength":1,"maxLength":60},
   "external_id":{"type":"string","maxLength":120},
   "tags":{"type":"array","maxItems":10,"items":{"type":"string","minLength":1,"maxLength":40}}}}',metadata);

SELECT m.post_id FROM post_metadata m
LEFT JOIN posts r ON r.id=m.post_id WHERE r.id IS NULL;