CODEWITHSUNDEEP
mysqlnode.js
user5113508
user5113508

Reputation:

How to store an Array in a table MySQL (For NodeJS)

I've recently started learning MySQL and I've been trying to apply it with Node to make a REST API instead of using the usual Mongo Node stack.

However, one problem I've run into is storing arrays and objects in MySQL. In Mongo it's super easy, and in MySQL, it seems it isn't as easy.

Basically I want to return a JSON with a structure like this;

{
  "name": "My Product",
  "desc": "Product description",
  "images": [
    {
      "caption": "Product Photo 1",
      "url": "http://"
    },
    {
      "caption": "Product Photo 2",
      "url": "http://"
    },
    {
      "caption": "Product Photo 3",
      "url": "http://"
    },
  ],
  
  "pricing": {
    "standard": {
      "title": "The Standard Product",
      "desc": "Standard Product Description",
      "prices": {
        "usd": 10.99,
        "eur": 9.99,
        "gbp": 8.99
      }
    },
    "deluxe": {
      "title": "The Deluxe Product",
      "desc": "Deluxe Product Description",
      "prices": {
        "usd": 19.99,
        "eur": 17.99,
        "gbp": 15.99
      }
    }
  }
}

However, I'm having some troubles understanding the concepts of achieving storage for properties like the images array.

I have read that the best practice is to store them in another table so that you can query them and such. But I'm not sure I understand this since if I'm adding a new table for every array/object I will end up with tons of them on larger scale projects?

I have also read about storing it as a JSON string but that doesn't really accomplish what I'm after, so I'm more so looking into the above solution.

Upvotes: 1

Views: 2344

Answers (2)

ruiquelhas
ruiquelhas

Reputation: 2025

Are you using MySQL 8.0? It supports a document store offering similar to MongoDB as well available via the X DevAPI, so you don't need to use the "low-level" JSON datatype if you don't want to, and still achieve the same.

Since it seems like you are using Node.js, there's also an X DevAPI client implementation you can use for that purpose.

Disclaimer: I'm the lead dev working in that X DevAPI implementation.

Upvotes: 1

Sunil Patel
Sunil Patel

Reputation: 265

https://scotch.io/tutorials/working-with-json-in-mysql Read this article

create datatype column as JSON and you can Dump this data

{
  "name": "My Product",
  "desc": "Product description",
  "images": [
    {
      "caption": "Product Photo 1",
      "url": "http://"
    },
    {
      "caption": "Product Photo 2",
      "url": "http://"
    },
    {
      "caption": "Product Photo 3",
      "url": "http://"
    },
  ],

  "pricing": {
    "standard": {
      "title": "The Standard Product",
      "desc": "Standard Product Description",
      "prices": {
        "usd": 10.99,
        "eur": 9.99,
        "gbp": 8.99
      }
    },
    "deluxe": {
      "title": "The Deluxe Product",
      "desc": "Deluxe Product Description",
      "prices": {
        "usd": 19.99,
        "eur": 17.99,
        "gbp": 15.99
      }
    }
  }
}

later you can query on this data here ref mysql doc https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Upvotes: 0

Related Questions