时间戳

首页 > 解决方案 > JavaScript + MariaDB：SQL 查询返回 json 对象数组并从响应中删除 `\` 正斜杠

javascript - JavaScript + MariaDB：SQL 查询返回 json 对象数组并从响应中删除 `\` 正斜杠

问题描述

我在 javascript 函数中将原始查询应用到我的 sql (mariaDB) 表中。

::: 更新问题 :::

下面是一个用于创建 sql 查询的 javascript 函数，我也尝试使用答案中建议的JSON_ARRAYandJSON_OBJECT方法。

public async getProducts() {
  try {
  let productTbl = '`products`';
  let prodImgTbl = '`product_images_ids`';
  let productImg = '`product_images`';

  let query = `SELECT 
  ${productTbl}.id AS productId,
  GROUP_CONCAT(JSON_ARRAY(JSON_OBJECT(
    "imageId", ${prodImgTbl}.id, 
    "imageUrl", ${productImg}.thumbnail_image
  ))) imageData,

  FROM ${productTbl} 
  JOIN ${prodImgTbl} ON ${prodImgTbl}.product_id = ${productTbl}.id
  JOIN ${productImg} ON ${productImg}.id = ${prodImgTbl}.product_image_id
  GROUP BY ${productTbl}.id `

  let records = await pool.query(query);
  return records
  } catch (error) {
  throw error;
  }
}

使用此查询，我在邮递员中得到如下结果，即添加\到每个键和值中。

this.productList = [
  {
    "productId": 3,
    "imageData": "[{\"imageId\": 17, \"imageUrl\": \"/assets/images/321/1/1/1/image.jpg\"}],[{\"imageId\": 18, \"imageUrl\": \"/assets/images/322/image.jpg\"}]",
  }
]

当我试图遍历上述集合并抛出以下错误时： TypeError: lists.imageData.map is not a function

this.productList.map((lists) => {
  lists.imageData.map((data) => {
    data.imageId
  })
})

所以我想要渲染的结果如下，这样我就可以遍历记录。

[ RowDataPacket {
    productId: 3,
    imageData [
      {
        imageId: 18,
        imageUrl: /assets/images/321/image.jpg
      },
      {
        imageId: 17,
        imageUrl: /assets/images/322/image.jpg
      }
    ]
  }
]

SQL Query 在管理员工具中运行

SELECT 
`products`.id AS productId,
GROUP_CONCAT(JSON_ARRAY(JSON_OBJECT(
  "imageId", `product_images_ids`.id, 
  "imageUrl", `product_images`.thumbnail_image
))) imageData
FROM `products`
JOIN `product_images_ids` ON `product_images_ids`.campaign_id = `products`.id
JOIN `product_images` ON `product_images`.id = `product_images_ids`.product_image_id
GROUP BY `products`.id

这是我在管理员中获得的结果截图。

我已经检查了json_arrayagg和json_objectagg但无法创建 json 对象数组。

PS：MySQL版本：5.5.5-10.3.22-MariaDB

标签： javascriptmysqlsqlmariadb

解决方案

放一个CONCAT里面GROUP_CONCAT并在这里创建JSON对象

我不确定 npmmariadb模块是否会弄清楚结构

let query = `SELECT 
  ${productTbl}.id AS productId,
  GROUP_CONCAT(
    CONCAT('{imageId:"'${prodImgTbl}'", imageUrl:"'${productImg}.thumbnail_image'"}')) imageData
  FROM ${productTbl} 

  JOIN ${prodImgTbl} ON ${prodImgTbl}.product_id = ${productTbl}.id
  JOIN ${productImg} ON ${productImg}.id = ${prodImgTbl}.product_image_id
  GROUP BY ${productTbl}.id`

如果这不起作用，请尝试将其转换为 JSON 类型（整个 GROUP_CONCAT 或其中的每个表达式）

CAST(
  CONCAT(
    '[', GROUP_CONCAT(
      CONCAT('{"imageId": "'${prodImgTbl}'", 
        "imageUrl": "'${productImg}.thumbnail_image'"}')),']') 
  AS JSON) imageData

如果您使用的是最新的 mariadb 版本，则有JSON_OBJECT

GROUP_CONCAT(JSON_OBJECT("imageId", ${prodImgTbl}, "imageUrl", ${productImg}.thumbnail_image)) imageData

- -编辑

你可以打电话JSON.parse给每个imageData

this.productList = map(pr => ({...pr, imageData: JSON.parse(pr.imageData)}))

显然之前有人问过类似的问题，在这里他们建议在查询中创建整个 JSON。

`SELECT JSON_OBJECT(
  'productId',
  ${productTbl}.id,
  'imageData',
  CAST(
    CONCAT(
      '[', GROUP_CONCAT(
        CONCAT('{"imageId": "'${prodImgTbl}'", 
          "imageUrl": "'${productImg}.thumbnail_image'"}')),']') 
  AS JSON)
  )
  FROM ${productTbl} 

  JOIN ${prodImgTbl} ON ${prodImgTbl}.product_id = ${productTbl}.id
  JOIN ${productImg} ON ${productImg}.id = ${prodImgTbl}.product_image_id
  GROUP BY ${productTbl}.id`

不幸的是，你仍然需要打电话JSON.parse

let records = JSON.parse(await pool.query(query));

还有 JSON_OBJECTAGG

let query = `SELECT 
  ${productTbl}.id AS productId,
  JSON_OBJECTAGG(${prodImgTbl}, ${productImg}.thumbnail_image) imageData
  FROM ${productTbl} 

  JOIN ${prodImgTbl} ON ${prodImgTbl}.product_id = ${productTbl}.id
  JOIN ${productImg} ON ${productImg}.id = ${prodImgTbl}.product_image_id
  GROUP BY ${productTbl}.id`

但我认为你仍然需要做一些连接才能将上面作为一个数组并以JSON.parse一种或另一种方式调用。

推荐阅读