purchase_list表items字段JSON格式购书数据技术问询

Hey there! Let’s walk through practical solutions for working with your book purchase data stored as JSON arrays in the items field of the purchase_list table. First, let’s recap the sample data you shared:

Sample JSON values from the items field:

[ 
  { "id": "901651", "supplier_id": "180", "price": "18.99", "product_id": "books", "name": "bookmate", "quantity": "1" }, 
  { "id": "1423326", "supplier_id": "180", "price": "53.99", "product_id": "books", "name": "classmate", "quantity": "5" } 
]

[{"id":"3811088","supplier_id":"2609","price":"22.99","product_id":"book","name":"classmate","quantity":"10"}]

Below are common tasks and their implementations for both MySQL (8.0+) and PostgreSQL, since these are the most widely used databases with robust JSON support:

If you need to view each book entry as a separate row (for easier querying/analysis), use these queries:

MySQL #

SELECT 
  pl.id AS purchase_id,
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.id')) AS book_item_id,
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) AS book_name,
  CAST(JSON_EXTRACT(item, '$.price') AS DECIMAL(10,2)) AS price,
  CAST(JSON_EXTRACT(item, '$.quantity') AS INT) AS quantity
FROM purchase_list pl
JOIN JSON_TABLE(
  pl.items,
  '$[*]' COLUMNS (
    item JSON PATH '$'
  )
) AS jt;

PostgreSQL #

SELECT 
  pl.id AS purchase_id,
  (item->>'id') AS book_item_id,
  (item->>'name') AS book_name,
  (item->>'price')::DECIMAL(10,2) AS price,
  (item->>'quantity')::INT AS quantity
FROM purchase_list pl,
     JSON_ARRAY_ELEMENTS(pl.items::JSON) AS item;

To get aggregated stats (like total units bought and money spent on "classmate" books):

MySQL #

SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) AS book_name,
  SUM(CAST(JSON_EXTRACT(item, '$.quantity') AS INT)) AS total_quantity,
  SUM(CAST(JSON_EXTRACT(item, '$.price') AS DECIMAL(10,2)) * CAST(JSON_EXTRACT(item, '$.quantity') AS INT)) AS total_spent
FROM purchase_list pl
JOIN JSON_TABLE(
  pl.items,
  '$[*]' COLUMNS (
    item JSON PATH '$'
  )
) AS jt
WHERE JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) = 'classmate'
GROUP BY JSON_UNQUOTE(JSON_EXTRACT(item, '$.name'));

PostgreSQL #

SELECT 
  (item->>'name') AS book_name,
  SUM((item->>'quantity')::INT) AS total_quantity,
  SUM((item->>'price')::DECIMAL(10,2) * (item->>'quantity')::INT) AS total_spent
FROM purchase_list pl,
     JSON_ARRAY_ELEMENTS(pl.items::JSON) AS item
WHERE (item->>'name') = 'classmate'
GROUP BY (item->>'name');

If you need to modify a value (e.g., update quantity of the book with id: 901651 to 3):

MySQL #

UPDATE purchase_list
SET items = JSON_REPLACE(
  items,
  JSON_UNQUOTE(JSON_SEARCH(items, 'one', '901651', NULL, '$[*].id')),
  JSON_OBJECT(
    'id', '901651',
    'supplier_id', '180',
    'price', '18.99',
    'product_id', 'books',
    'name', 'bookmate',
    'quantity', '3'
  )
)
WHERE JSON_SEARCH(items, 'one', '901651', NULL, '$[*].id') IS NOT NULL;

PostgreSQL #

WITH book_index AS (
  SELECT 
    pl.id AS purchase_id,
    idx - 1 AS array_idx -- JSON arrays are 0-indexed in PostgreSQL's jsonb_set
  FROM purchase_list pl,
       JSON_ARRAY_ELEMENTS(pl.items::JSON) WITH ORDINALITY AS item(data, idx)
  WHERE (data->>'id') = '901651'
)
UPDATE purchase_list pl
SET items = JSONB_SET(
  pl.items::JSONB,
  ARRAY[array_idx::TEXT, 'quantity'],
  '"3"'
)
FROM book_index bi
WHERE pl.id = bi.purchase_id;

For long-term maintainability, you might want to move the JSON data to a dedicated relational table. First create the table:

CREATE TABLE purchase_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_id INT,
  book_item_id VARCHAR(255),
  supplier_id VARCHAR(255),
  price DECIMAL(10,2),
  product_id VARCHAR(255),
  name VARCHAR(255),
  quantity INT,
  FOREIGN KEY (purchase_id) REFERENCES purchase_list(id)
);

Then insert the data:

MySQL #

INSERT INTO purchase_items (
  purchase_id, book_item_id, supplier_id, price, product_id, name, quantity
)
SELECT 
  pl.id,
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.id')),
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.supplier_id')),
  CAST(JSON_EXTRACT(item, '$.price') AS DECIMAL(10,2)),
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.product_id')),
  JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')),
  CAST(JSON_EXTRACT(item, '$.quantity') AS INT)
FROM purchase_list pl
JOIN JSON_TABLE(
  pl.items,
  '$[*]' COLUMNS (
    item JSON PATH '$'
  )
) AS jt;

PostgreSQL #

INSERT INTO purchase_items (
  purchase_id, book_item_id, supplier_id, price, product_id, name, quantity
)
SELECT 
  pl.id,
  (item->>'id'),
  (item->>'supplier_id'),
  (item->>'price')::DECIMAL(10,2),
  (item->>'product_id'),
  (item->>'name'),
  (item->>'quantity')::INT
FROM purchase_list pl,
     JSON_ARRAY_ELEMENTS(pl.items::JSON) AS item;

If you have a specific task in mind (like filtering by supplier, handling edge cases with invalid JSON, etc.), feel free to elaborate!

内容的提问来源于stack exchange，提问作者dreamer