json 변환 쿼리

[simhani1]

-- 1) 실행 시각 고정(모든 row가 동일 created_at/updated_at)
SET @now := NOW();

-- 2) 아래에 네가 준 JSON을 그대로 붙여넣기
SET @payload := '{
  "blogs": [
    {
      "_index": "content-index",
      "_id": "https://devocean.sk.com/blog/index.do",
      "_score": null,
      "_source": {
        "id": "https://devocean.sk.com/blog/index.do",
        "lastUpdatedDate": "2026-02-20",
        "dataType": "blog",
        "cron": true,
        "rssURL": "https://devocean.sk.com/blog/rss.do",
        "title": "데보션",
        "lastUpdated": 1771603295000
      },
      "sort": [1771603295000]
    }
    /* ... (나머지 전부 그대로) ... */
  ]
}';

-- 3) INSERT
INSERT INTO techmoa.blog (
  created_at,
  updated_at,
  link,
  logo_url,
  rss_link,
  operation_status,
  name
)
SELECT
  @now AS created_at,
  @now AS updated_at,
  jt.id AS link,
  jt.id AS logo_url,
  jt.rss_url AS rss_link,
  'PAUSED' AS operation_status,
  LEFT(jt.title, 20) AS name   -- name 컬럼이 VARCHAR(20)이라 안전하게 잘라 넣기
FROM JSON_TABLE(
  @payload,
  '$.blogs[*]._source'
  COLUMNS (
    id       VARCHAR(600) PATH '$.id',
    rss_url  VARCHAR(600) PATH '$.rssURL',
    title    VARCHAR(255) PATH '$.title'
  )
) AS jt;