Storing and querying JSON from a database

Question

I've heard about MongoDB, but I'm not sure I fully understand the concept.

If I have multiple JSON objects stored in MongoDB:

[{"id": "peter",
  "age": "12",
  "gender": "male"},
 {"id": "gemma",
  "age": "12",
  "gender": "female"},
 {"id": "paul",
  "age": "13",
  "gender": "male"}]

How would I be able to query all JSON objects with age >= 12?

Solution 1

First of all, understand that JSON is just a serialization technique. In and of itself, this serialization method probably should not determine your persistence medium. Looking at your question on the surface, it seems like what you are looking for is a typical relational storage database where you can use SQL to query against your data in a flexible manner.

Serializing/deserializing JSON data for storage into or for presentation after retrieval from such a relational database is trivial in pretty much any programming language.

Now if you truly need to store various snippets of JSON documents (or any other sort of document) that don't really have a fixed structure, that is really when you typically would start looking at a NoSQL type of solution such as MongoDB. One other possible such scenario for using the more popular NoSQL databases is when you are dealing with massive amounts of data and need to scale horizontally (i.e. the data is so large you need to scale the database across multiple servers). Many NoSQL systems make this much easier to do than traditional relational DB's. Of course in such a scenario, you would then need to evaluate those tools based on the functionality they provide in allowing you to read, write, and query data in the most useful manner for your use case(s).

Solution 2

Starting from 5.7, MySQL now has native JSON datatype. Assuming your table is called students and the JSON column is called student, in MySQL 5.7 your select can be written as

SELECT * FROM students WHERE JSON_EXTRACT(student, '$.age') = 12;

Solution 3

MongoDB stores data in BSON format which is similar to JSON. You can store data in JSON format and can make a query on any field. You can even index on a particular field which is used for major queries.

You are not limited to MongoDB, Seeing your question i think any of the Document-store will suit your needs. You read more here :- http://en.wikipedia.org/wiki/Document-oriented_database

Solution 4

If all your objects have the same fields (id, age, gender, etc.) then you should store the objects as rows of some relational database (e.g. MySQL).

Solution 5

A mongodb shell query for that might look like this:

db.people.find({"age": {gt:12 }});

View more solutions

Comments

[Jakob]

thank you mike, I'm just doing some field-work on storing facebook user objects for a research project - what does scaling horizontally mean?

[Mike Brant]

@Jakob By scaling horizontally, I mean that you are dealing such a large workload, that you need multiple nodes/servers to handle it. Horizontal scaling is historically more problematic for relational databases than for NoSQL document stores.

[Maury Markowitz]

It is worth pointing out this is more than just a string with some logic attached. MySQL stores this data in a way that allows it to get individual fields quickly - that $.age is a single fetch, it doesn't have to parse the original JSON to extract that value.