MySQL: save JSON data via stored procedure in Node.js

Question

I am trying to save some JSON data with a stored procedure in MySQL and Node.

I have post, post_tag and tag tables.

DROP TABLE IF EXISTS post_tag;
DROP TABLE IF EXISTS post;
DROP TABLE IF EXISTS tag;
DROP PROCEDURE IF EXISTS select_all_posts;
DROP PROCEDURE IF EXISTS insert_post;

CREATE TABLE post (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(45) NULL,
  body TEXT NULL,
  my_data TEXT NULL,
  PRIMARY KEY (id));

CREATE TABLE tag (
  id INT NOT NULL AUTO_INCREMENT,
  tag VARCHAR(45) NULL UNIQUE,
  PRIMARY KEY (id));

CREATE TABLE post_tag (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  INDEX fk_post_tag_tag1_idx (tag_id ASC) VISIBLE,
  INDEX fk_post_tag_post_idx (post_id ASC) VISIBLE,
  CONSTRAINT fk_post_tag_post
    FOREIGN KEY (post_id)
    REFERENCES post (id),
  CONSTRAINT fk_post_tag_tag1
    FOREIGN KEY (tag_id)
    REFERENCES tag (id));

INSERT INTO post (id, title, body) VALUES (1, 'post 1', "Post body");
INSERT INTO post (id, title, body) VALUES (2, 'post 2', "Post body");
INSERT INTO tag (id, tag) VALUES (1, 'tag 1');
INSERT INTO tag (id, tag) VALUES (2, 'tag 2');
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 1);
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 2);
INSERT INTO post_tag (post_id, tag_id) VALUES (2, 1);

Now I want to save one post with two tags, so I created this stored procedure:

-- Stored procedure to insert post and tags
DROP PROCEDURE IF EXISTS insert_post;
DELIMITER $$
CREATE PROCEDURE insert_post(
  IN my_data JSON
)
BEGIN
  -- Declare iterator variable to use it later on in the loop
  DECLARE i INT DEFAULT 0;

  -- Retrieve values from JSON
  SET @title = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.title'));
  SET @body = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.body'));
  SET @tags = JSON_EXTRACT(my_data, '$.tags');
  SET @json = JSON_UNQUOTE(my_data);
  -- Insert post
  INSERT INTO post (title, body, my_data) VALUES (
    @title,
    @body,
    @json);
  -- Retrieve inserted id to reuse it in post_tag
  SET @last_post = LAST_INSERT_ID();

  -- Get tags length for the loop
  SET @tags_length = JSON_LENGTH(@tags);
  -- Execute loop over tags length
  WHILE i < @tags_length DO
    -- Retrieve current tag from tags array
    SET @tag = JSON_UNQUOTE(JSON_EXTRACT(my_data, CONCAT('$.tags[',i,'].tag')));

    -- Insert tag
    INSERT INTO tag (tag) VALUES (
    @tag
    ) ON DUPLICATE KEY UPDATE tag = @tag;
    -- -- Retrieve inserted tag to reuse it on post_tag
    SET @last_tag = LAST_INSERT_ID();
    -- Insert retrieved post_id and tag_id into post_tag
    INSERT INTO post_tag (post_id, tag_id) VALUES (
      @last_post,
      @last_tag
    ) ON DUPLICATE KEY UPDATE post_id = @last_post, tag_id = @last_tag;
    -- -- Add step to iterator
    SELECT i + 1 INTO i;
  END WHILE;
END $$
DELIMITER ;

I can test it with SQL directly:

CALL insert_post('{"title":"My post","body":"postbody","tags":[{"tag":"tag 3"}]}');

This will succeed, as there are no repeated tags.

CALL insert_post('{"title":"My post","body":"postbody","tags":[{"tag":"tag 1"}]}');

This will fail, as there is already a tag 1.

How do I fix this?

Hi @Akina, don't understand exactly what you mean. How would you use REPLACE here? — Emille C.
– Emille C., Commented May 21, 2020 at 12:33
Just found out that its now a problem with JSON_UNQUOTE, but with post_tag having two keys and ON DUPLICATE KEY UPDATE — Emille C.
– Emille C., Commented May 21, 2020 at 12:38

Akina · Accepted Answer · 2020-05-21 12:41:58Z

3

CREATE PROCEDURE insert_post(
  IN my_data JSON
)
BEGIN
  -- Declare iterator variable to use it later on in the loop
  DECLARE i INT DEFAULT 0;

  -- Retrieve values from JSON
  SET @title = JSON_EXTRACT(my_data, '$.title');
  SET @body = JSON_EXTRACT(my_data, '$.body');
  SET @tags = JSON_EXTRACT(my_data, '$.tags');
  SET @json = JSON_UNQUOTE(my_data);
  -- Insert post
  INSERT INTO post (title, body, my_data) VALUES (
-- ****    UNQUOTE values    ****
    JSON_UNQUOTE(@title),
    JSON_UNQUOTE(@body),
    @json);
  -- Retrieve inserted id to reuse it in post_tag
  SET @last_post = LAST_INSERT_ID();

  -- Get tags length for the loop
  SET @tags_length = JSON_LENGTH(@tags);
  -- Execute loop over tags length
  WHILE i < @tags_length DO
    -- Retrieve current tag from tags array
    SET @tag = JSON_EXTRACT(my_data, CONCAT('$.tags[',i,'].tag'));

    -- Insert tag
    INSERT INTO tag (tag) VALUES (
-- ****    UNQUOTE value    ****
      JSON_UNQUOTE(@tag)
    ) ON DUPLICATE KEY UPDATE tag = JSON_UNQUOTE(@tag);
    -- Retrieve inserted tag to reuse it on post_tag
-- ****    ODKU and LAST_INSERT_ID are not relative!!! removed.    ****
--    SET @last_tag = LAST_INSERT_ID();
-- ****    Retrieve according `id`    ****
    SELECT id INTO @last_tag
    FROM tag
    WHERE tag = JSON_UNQUOTE(@tag);
    -- Insert retrieved post_id and tag_id into post_tag
    INSERT INTO post_tag (post_id, tag_id) VALUES (
      @last_post,
      @last_tag
    ) ON DUPLICATE KEY UPDATE post_id = @last_post, tag_id = @last_tag;
    -- Add step to iterator
    SELECT i + 1 INTO i;
  END WHILE;
END

fiddle

PS. You may use ON DUPLICATE KEY UPDATE tag = VALUES(tag); instead of ON DUPLICATE KEY UPDATE tag = JSON_UNQUOTE(@tag);. And the same for post_tag. fiddle.

edited May 21, 2020 at 12:41

answered May 21, 2020 at 12:35

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Emille C. Over a year ago

Interesting! What do you mean with «LAST_INSERT_ID are not relative»?

Emille C. Over a year ago

also, what is ODKU?

Akina Over a year ago

@EmilleC. You try to insert. If no duplicate, insertion performed, new id generated and then retrieved. But what if duplicate was detected? Update performed instead. Does INSERT was performed? no. Does last inserted id was generated and it exists now? no. LAST_INSERT_ID() will return zero. Which results foreing key violatoin. what is ODKU ON DUPLICATE KEY UPDATE abbreviation.

Emille C. Over a year ago

really good explanation: update doesn't generate LAST_INSERT_ID()… Thanks!

Collectives™ on Stack Overflow

MySQL: save JSON data via stored procedure in Node.js

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related