Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing and querying JSON from a database

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?

like image 448
Jakob Avatar asked Oct 04 '13 20:10

Jakob


People also ask

How do I query JSON data in SQL?

To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

Can you query JSON data?

You can query JSON data using a simple dot notation or, for more functionality, using SQL/JSON functions and conditions. You can create and query a data guide that summarizes the structure and type information of a set of JSON documents.


2 Answers

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).

like image 53
Mike Brant Avatar answered Sep 23 '22 16:09

Mike Brant


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; 
like image 30
Sophia Feng Avatar answered Sep 21 '22 16:09

Sophia Feng