17

I have this project which stores product details from amazon into the database.

Just to give you an idea on how big it is:

[
  {
    "title": "Genetic Engineering (Opposing Viewpoints)",
    "short_title": "Genetic Engineering ...",
    "brand": "",
    "condition": "",
    "sales_rank": "7171426",
    "binding": "Book",
    "item_detail_url": "http://localhost/wordpress/product/?asin=0737705124",
    "node_list": "Books > Science & Math > Biological Sciences > Biotechnology",
    "node_category": "Books",
    "subcat": "",
    "model_number": "",
    "item_url": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=128",
    "details_url": "http://localhost/wordpress/product/?asin=0737705124",
    "large_image": "http://localhost/wordpress/wp-content/plugins/ecom/img/large-notfound.png",
    "medium_image": "http://localhost/wordpress/wp-content/plugins/ecom/img/medium-notfound.png",
    "small_image": "http://localhost/wordpress/wp-content/plugins/ecom/img/small-notfound.png",
    "thumbnail_image": "http://localhost/wordpress/wp-content/plugins/ecom/img/thumbnail-notfound.png",
    "tiny_img": "http://localhost/wordpress/wp-content/plugins/ecom/img/tiny-notfound.png",
    "swatch_img": "http://localhost/wordpress/wp-content/plugins/ecom/img/swatch-notfound.png",
    "total_images": "6",
    "amount": "33.70",
    "currency": "$",
    "long_currency": "USD",
    "price": "$33.70",
    "price_type": "List Price",
    "show_price_type": "0",
    "stars_url": "",
    "product_review": "",
    "rating": "",
    "yellow_star_class": "",
    "white_star_class": "",
    "rating_text": " of 5",
    "reviews_url": "",
    "review_label": "",
    "reviews_label": "Read all ",
    "review_count": "",
    "create_review_url": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=132",
    "create_review_label": "Write a review",
    "buy_url": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=19186",
    "add_to_cart_action": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/add_to_cart.php",
    "asin": "0737705124",
    "status": "Only 7 left in stock.",
    "snippet_condition": "in_stock",
    "status_class": "ninstck",
    "customer_images": [
      "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg",
      "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/31FIM-YIUrL.jpg",
      "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg",
      "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg"
    ],
    "disclaimer": "",
    "item_attributes": [
      {
        "attr": "Author",
        "value": "Greenhaven Press"
      },
      {
        "attr": "Binding",
        "value": "Hardcover"
      },
      {
        "attr": "EAN",
        "value": "9780737705126"
      },
      {
        "attr": "Edition",
        "value": "1"
      },
      {
        "attr": "ISBN",
        "value": "0737705124"
      },
      {
        "attr": "Label",
        "value": "Greenhaven Press"
      },
      {
        "attr": "Manufacturer",
        "value": "Greenhaven Press"
      },
      {
        "attr": "NumberOfItems",
        "value": "1"
      },
      {
        "attr": "NumberOfPages",
        "value": "224"
      },
      {
        "attr": "ProductGroup",
        "value": "Book"
      },
      {
        "attr": "ProductTypeName",
        "value": "ABIS_BOOK"
      },
      {
        "attr": "PublicationDate",
        "value": "2000-06"
      },
      {
        "attr": "Publisher",
        "value": "Greenhaven Press"
      },
      {
        "attr": "SKU",
        "value": "G0737705124I2N00"
      },
      {
        "attr": "Studio",
        "value": "Greenhaven Press"
      },
      {
        "attr": "Title",
        "value": "Genetic Engineering (Opposing Viewpoints)"
      }
    ],
    "customer_review_url": "http://localhost/wordpress/wp-content/ecom-customer-reviews/0737705124.html",
    "flickr_results": [
      "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/5105560852_06c7d06f14_m.jpg"
    ],
    "freebase_text": "No around the web data available yet",
    "freebase_image": "http://localhost/wordpress/wp-content/plugins/ecom/img/freebase-notfound.jpg",
    "ebay_related_items": [
      {
        "title": "Genetic Engineering (Introducing Issues With Opposing Viewpoints), , Good Book",
        "image": "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/140.jpg",
        "url": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=12165",
        "currency_id": "$",
        "current_price": "26.2"
      },
      {
        "title": "Genetic Engineering Opposing Viewpoints by DAVID BENDER - 1964 Hardcover",
        "image": "http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/140.jpg",
        "url": "http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=130",
        "currency_id": "AUD",
        "current_price": "11.99"
      }
    ],
    "no_follow": "rel=\"nofollow\"",
    "new_tab": "target=\"_blank\"",
    "related_products": [],
    "super_saver_shipping": "",
    "shipping_availability": "",
    "total_offers": "7",
    "added_to_cart": ""
  }
]

So the structure for the table is:

  • asin
  • title
  • details (the product details in json)

Will the performance suffer if I have to store like 10,000 products? Is there any other way of doing this? I'm thinking of the following, but the current setup is really the most convenient one since I also have to use the data on the client side:

  • store the product details in a file. So something like ASIN123.json
  • store the product details in one big file. (I'm guessing it will be a drag to extract data from this file)
  • store each of the fields in the details in its own table field

Thanks in advance!

UPDATE

Thanks for the answers! I just want to add some more details to my question. First, the records are updated for a specific interval. Only specific data such as the price or the title are updated.

Second, I'm also using the json encoded data in the client-side so I thought at first it would be easier to just have it json encoded so I can easily use it in the client side without having to convert. Does this change your opinion about simply storing the fields in a regular table field in an RDBMS setup?

3
  • 4
    Storing a JSON blob limits searchability of that data – imagine I want to find all products where details.amount < 40 and details.node_category = "Books". A NoSQL database could provide this quite easily. I also enjoyed the reference to the White Star class ;-)
    – amon
    Oct 24, 2013 at 8:09
  • I've addressed your update in my answer Oct 28, 2013 at 9:32
  • 1
    It is absolutely wise! Looks like the two answers given are stuck in their RDBMS past and are not open to the powerful and extremely efficient concepts intorduced by NOSQL.
    – user197803
    Sep 25, 2015 at 4:57

6 Answers 6

33

Size is not so much of an issue, the ability to query and maintain the data however is.

If, for example, Greenhaven Press decides they want to change their name to Greenhaven Press International, you'll have to find the record, deserialize it, change it, serialize it, pump it back into the database.

Consider this: does storing these objects as serialized data offer you a clear added value over storing it in a relational form? If the answer is no, then it might not be worth the hassle.

UPDATE

As far as your update of your question goes: I'm inclined to say no, it makes little or no difference. Whether you update one field or all of them in this json string is irrelevant because the whole process is identical.

Don't forget that your requirements might change; even though you're using json on the client side now doesn't mean you'll need json in the future. Storing your data in a relational form guarantees technology-independence while preserving relationships, data constraints and queryable metadata: this is where the true value of a relational db lies. Discarding those advantages will neither give you a performance gain nor make your application more scalable or flexible.

5
  • 4
    "you'll have to find the record, deserialize it, change it, serialize it, pump it back into the database." In light of functions like JSON_SET or JSON_MERGE_PATCH this is no longer an issue. "does storing these objects as serialized data offer you a clear added value over storing it in a relational form?" I would probably start from the other way. Does storing these objects as normalized data offer you clear added value over storing it in a raw form? Normalizing data comes at a cost, especially if you normally operate on document model. Mar 19, 2020 at 10:59
  • Imagine you have a front-end application where the default natural format is the JSON / document object model. JSON field allows you simply to save that data without the need of doing the normalization transformation. Imagine now that your user wants to update his document. You can simply fed him with JSON. If you have normalized data you would have to denormalize it first. In this case using raw JSON saved you need of being responsible for normalization/denormalization. As always there is no best fit solution for all cases, but I believe JSON approach is underestimated. Mar 19, 2020 at 11:08
  • @NeverEndingQueue if you "normally operate on a document model", you're probably not going to start out using a relational database. Neither relational nor non-relational databases are a silver bullet in and of themselves. They are merely tools in a toolbox and their use and respective advantages or disadvantages depend on the business case. My answer was relevant in its time. Perhaps I would answer it differently now, since e.g. SQL server has more options to work with JSON today than it did back then. Perhaps not. May 17, 2021 at 11:22
  • As you've rightly pointed out there is no silver bullet. My idea was mostly around taking the JSON/BLOB/STRING you name it and store it in RDBMS. You don't need anything other than field to store text. Today you have possibility to manipulate, extract, index JSON data, this was introduced in MySQL in 2014. Personally I am heavy user of these functions. Yet, more complicated searches on complex documents still require solutions like Solr, ES, making the RDBMS JSON features somewhat irrelevant. Despite all that you still wouldn't use Solr/ES as a data source / storage, but search back-end. May 18, 2021 at 7:28
  • ... so using RDBMS (even if it's id,text pair) even if you don't normalize your data, only for its storage capabilities and guarantees, then using 3rd party on top of the RDBMS is very valid idea today as well as 10 years ago, hence my suggestion about thinking about valid normalization approach first. May 18, 2021 at 7:30
5

Is it wise to store a big lump of json on a database row?

No, generally it is a bad idea to store multi-valued data in one cell of a relational database because it goes against the basic structure/rules of RDBMS (as mentioned in the other answer).

However, you may look into the possibility of using one of the non-relational (no-sql) databases, such as MongoDB, which will help you store JSON objects and search for data inside those objects.

2
  • 5
    IIRC - or PostgreSQL that has a new datatype especially for storing JSON data in a column and allowing you to query inside it (as if it was a dynamic set of columns)
    – gbjbaanb
    Oct 24, 2013 at 12:13
  • 1
    @gbjbaanb thanks for the info, it was news for me. Reference: postgresql.org/docs/9.2/static/datatype-json.html Oct 24, 2013 at 12:26
5

Assuming you don't have an immediate need to query over those JSON fields, then there's nothing wrong with this approach, so long as the performance hit of deserializing/serializing isn't a problem for your program.

Later on, let's say the requirement comes in to order/filter based on that "sales_rank" JSON field. You'd write a program to run over the data and deserialize the object, copying out that field to a dedicated column in the table, which would then be indexed. You'll need a custom serialization/update routine to make sure you keep this discreet column maintained in sync w/ the JSON object, but that's not hard.

This kind of hybrid approach can be very useful when you are still figuring out the app's functionality, as you get the fast development speed of NoSQL, with the benefit of relational modeling for the fields that need it.

2
  • 2
    many RDBMS allow you to create indexes virtual/indexed columns that allow you to target those element in complex structures when you need to report on them later. You get the presentation performance of not having to constantly recompose you data and the reporting/query performance of the normalized data. Sep 25, 2015 at 16:55
  • 1
    A college of mine just recently showed me that with the new JSON type in SQL Server, you can have dedicated computed columns and add indexes to them. Powerful and flexible.
    – GHP
    Jun 20, 2017 at 19:25
3

It is sometimes a better fit for the application to store a complete document as one entity. It is not a coincidence that nosql and document databases are on the rise.

If you are aware of the drawbacks it can be an ok approack but you should also consider a real document database instead of inventing your own.

1
  • I somewhat agree... there is a reason that more relational databases are starting to add special field types and functions to support complex JSON and XML queries. Some data just doesn't need to be normalized if the only thing you are ever going to do is present it as hierarctical data anyway. There are advantages to just using blob storage with your relational data versus running two seperate databases. Sep 25, 2015 at 16:52
0

SQL Server 2016 also allows you to store JSON and process it, almost like all the NOSQL dbs out there. I still agree with most people out there that RDBMS approach is probably the best route and it gives you the most flexibility.

With the SQL Server 2016 JSON Approach, I do not know how much power it has (editing, deletions; I know you can search for fields/properties in the json rows) but its an option worth investigating.

https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-2017

0

The most important thing to think about before storing any data is not how to store it - it's what you're going to do with it afterwards.

If all you're ever going to do is retrieve the whole slab of JSON from records found some other way (i.e. using other fields in the record) and push that JSON data out to the client, then storing it all in one, big "blob" is fine. You'll only need to retrieve one (albeit big) data field, rather than reading a whole bunch of them and then piecing them together to send to the client.

If you're going to query the table based on some fields within the JSON then you really need to be looking at using proper, JSON-aware Data Types, with indexing to match. Without this, you'll Table Scan every single time and that's Bad or, without JSON-aware types, regular "string" searching will find loads of false-positives (JSON text that looks like what you want, but is actually in completely unrelated fields), which is probably worse.

If you're going to start updating bits of the JSON independently of other bits, then the trouble really starts.
Because you've got all of that data in a single field, you have to update the entire field just to change a single character of it. The bigger the data in the field, the bigger the load on the database. I'd strongly recommend extracting such fields out of the JSON and into fields in their own right, to reduce this "update" overhead.

Not the answer you're looking for? Browse other questions tagged or ask your own question.