JavaScript + MariaDB: SQL query to return array of json objects and remove `\` forward slash from response

Question

I am applying a raw query into my sql (mariaDB) tables in javascript function.

::: Updated Question :::

Below is a javascript function to create a sql query in which i have also tried with the JSON_ARRAY and JSON_OBJECT methods as suggested in the answer.

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;
  }
}

Using this query i am getting the result in postman as below which is adding the \ to the each key and value.

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

As i tried to loop through the above collection and throws the error that: TypeError: lists.imageData.map is not a function

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

So the result that i wanted to render is as below, so that i can loop through the records.

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

SQL Query run in the adminer tool

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

And here is a result screenshot that i am getting in adminer.

I have checked with the json_arrayagg and also json_objectagg but can not able to create an array of json objects.

P.S: MySQL version: 5.5.5-10.3.22-MariaDB

you can build a json in mysql or you do it in javascript,

nbk
– nbk

2020-06-08 10:12:08 +00:00
Commented Jun 8, 2020 at 10:12 — nbk
– nbk, Commented Jun 8, 2020 at 10:12

Józef Podlecki · Accepted Answer · 2020-06-09 15:05:10Z

1

Put a CONCATinside GROUP_CONCAT and create JSON object here

I am not sure if npm mariadb module will figure out the structure

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`

If that doesn't work try cast it to JSON type ( the whole GROUP_CONCAT or every expression inside it)

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

If you are using the latest mariadb version there is JSON_OBJECT

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

---Edit

You could call JSON.parse on each imageData

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

Apparently there was similar question asked before and here they suggest creating the whole JSON in query.

`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`

Unfortunately you'd still have to call JSON.parse

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

There is also 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`

But I think you'd still have to do some concatenation to make above as an array and call JSON.parse one way or another.

edited Jun 9, 2020 at 15:05

answered Jun 8, 2020 at 10:45

Józef Podlecki

11.4k5 gold badges33 silver badges55 bronze badges

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

3 Comments

TMA Over a year ago

Hello sir, I am getting the result using your suggested answer but it is not a perfect json objects. Please review my updated question. Thanks.

Józef Podlecki Over a year ago

Updated answer.

TMA Over a year ago

I guess in the future queries it will become more difficult to manage by this way, and i found one good package of npm [ sequelize.org/master ]

Collectives™ on Stack Overflow

JavaScript + MariaDB: SQL query to return array of json objects and remove `\` forward slash from response

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related