外部 API のデータで行を拡充する

INSTALL EXTENSION vsql_http;

ALTER TABLE orders ADD COLUMN shipping_status VARCHAR(100);

UPDATE orders
SET shipping_status = JSON_VALUE(
  JSON_UNQUOTE(JSON_EXTRACT(
    CONVERT(
      vsql_http.http_get(
        CONCAT('https://api.shipping.com/track/', tracking_number)
      ) USING utf8mb4
    ),
    '$.content'
  )),
  '$.status'
)
WHERE tracking_number IS NOT NULL
  AND shipping_status IS NULL
LIMIT 50;

SELECT id, customer_id, shipping_status
FROM orders
WHERE shipping_status = 'delivered'
ORDER BY updated_at DESC;

-- Fetch the current USD/EUR exchange rate and apply it to each row
SET @rate_resp = CONVERT(
  vsql_http.http_get('https://api.exchangerate.example.com/latest?base=USD')
  USING utf8mb4
);
SET @rate_body = JSON_UNQUOTE(JSON_EXTRACT(@rate_resp, '$.content'));
SET @eur_rate = CAST(JSON_VALUE(@rate_body, '$.rates.EUR') AS DECIMAL(10,6));

SELECT
  id,
  amount_usd,
  ROUND(amount_usd * @eur_rate, 2) AS amount_eur
FROM invoices
WHERE currency = 'USD'
LIMIT 100;

-- Only enrich rows missing geocoordinates
UPDATE locations
SET
  lat = CAST(JSON_VALUE(
    JSON_UNQUOTE(JSON_EXTRACT(
      CONVERT(
        vsql_http.http_get(
          CONCAT('https://api.geocode.example.com/json?address=',
                 vsql_http.url_encode(address))
        ) USING utf8mb4
      ),
      '$.content'
    )),
    '$.lat'
  ) AS DECIMAL(10,7)),
  lng = CAST(JSON_VALUE(
    JSON_UNQUOTE(JSON_EXTRACT(
      CONVERT(
        vsql_http.http_get(
          CONCAT('https://api.geocode.example.com/json?address=',
                 vsql_http.url_encode(address))
        ) USING utf8mb4
      ),
      '$.content'
    )),
    '$.lng'
  ) AS DECIMAL(10,7))
WHERE lat IS NULL
  AND address IS NOT NULL
LIMIT 25;

SET @resp = CONVERT(
  vsql_http.http_get('https://api.example.com/data/123') USING utf8mb4
);

SELECT
  CASE
    WHEN @resp IS NULL          THEN 'connection_failed'
    WHEN JSON_VALUE(@resp, '$.status') = '200' THEN
      JSON_UNQUOTE(JSON_EXTRACT(@resp, '$.content'))
    WHEN JSON_VALUE(@resp, '$.status') = '404' THEN 'not_found'
    ELSE CONCAT('error_', JSON_VALUE(@resp, '$.status'))
  END AS result;

UPDATE orders
SET shipping_status = JSON_VALUE(
  JSON_UNQUOTE(JSON_EXTRACT(
    CONVERT(vsql_http.http_get(
      CONCAT('https://api.shipping.com/track/', tracking_number)
    ) USING utf8mb4),
    '$.content'
  )),
  '$.status'
)
WHERE shipping_status IS NULL
LIMIT 50;