How to convert MySQL rows into formatted json objects

Question

For example say you have a user that can have many products and a product could have one brand, your rows come back from MySQL in a json object similar to this

[
  {
    id: 1,
    name: "robert",
    productID: 150,
    productName: "apple",
    brandID: 3,
    brandName: 'sonoma'
  },
  {
    id: 1,
    name: "robert",
    productID: 22,
    productName: "ice",
    brandID: 3,
    brandName: 'sonoma'
  },
  {
    id: 2,
    name: "willy",
    productID: 150,
    productName: "apple",
    brandID: 3,
    brandName: 'sonoma'
  }
]

but you want it to look like:

    [
      {
        id: 1,
        name: "robert",
        Products: [
          {
            productID: 150,
            productName: "apple",
            Brand: {
              brandID: 3,
              brandName: 'sonoma'
            }
          },
          {
            productID: 22,
            productName: "ice",
            Brand: {
              brandID: 3,
              brandName: 'sonoma'
            }
          }
        ]
      },
      {
        id: 2,
        name: "willy",
        Products: {
          productID: 150,


    productName: "apple",
      Brand: {
        brandID: 3,
        brandName: 'sonoma'
      }
    }
  }
]

Are there any tools you can use to automatically format things in this manner? Or should I just do it myself with a .map. It seems like this would have had to be done many times before, and i'm sure theres a good way to do it.

Thank you!

edit im not asking how to do this inside of a language like php, I'm asking how to do it in raw SQL.

@AnuragSrivastava so query users, then for each user run a query? If thats not what you mean id love an example! Thank you! — Sammy Roberts, Mar 04 '19 at 21:44
Yes something along those lines. Would be more efficient than doing it client side. — Anurag Srivastava, Mar 04 '19 at 21:46
Why is `Products` an array for robert, but a single object for willy? You should use a consistent structure. — Barmar, Mar 04 '19 at 22:02
Collapsing arrays of one element to just the element generally complicates all the code that tries to use the resulting data. I suggest you avoid it, and just use a single-element array. — Barmar, Mar 05 '19 at 20:53

How to convert MySQL rows into formatted json objects

0 Answers0