Storing JSON Data Type In MySQL

What is JSON ?

  • Json (Java script object notation ) is a lightweight data-interchange format.
  • It’s easy for humans to read and write.It’s easy for machines to parse and generate.
  • It is used primarily to transmit data between a server and web application, as an alternative to XML.
  • If you want more details click here JSON

Keys and Values :

These are the two primary parts of the JSON.

  • Key: A key is always a string enclosed in quotation marks.
  • Value: A value can be a string, number, boolean expression, array, or object.
  • Key/Value Pair: A key value pair it  follows a specific syntax, The key followed by a colon followed by the value. Key/value pairs are comma separated.

MySQL 5.7 Supports JSON data types. Previous version of MySQL stores JSON datas as text file in MySQL. We have seen clients storing the JSON files in text format inside MySQL and later they are processed by Elastic Search or some other external processing.

JSON format is one of the important feature in MySQL

JSON Data Types:

  • Array: An associative array of values.
  • Boolean: True or false.
  • Number: An integer.
  • Object: An associative array of key/value pairs.
  • String: Several plain text characters which usually form a word.

Array:

  • An array is an ordered collection of values.An array beings with [ (left bracket) and ends with ] (right bracket).
  • The values are separated by , (comma).

 

array1 (1).jpg

Array

Example :

[“abc”, 10, null, true, false]

arr_1.png

  • A JSON array takes a (possibly empty) list of values and returns a JSON array containing those values.

arr_2.png

Boolean & Number :

  • A value can be a string in double quotes, or number, or true or false or null , or an object.these structures can be nested.

 

boolian (1).jpg

Example :

[99, {“id”: “HK500”, “cost”: 75.99}, [“hot”, “cold”]]
{“k1”: “value”, “k2”: [10, 20]}

Object :

  • An object is an un ordered set of names and values.A object start with { (left brace) and ends with } (right brace).
  • The each name followed by : (colon) and the name and value pairs are separated by by , (comma).

 

object (1).jpg

Object

Example :

{“k1”: “value”, “k2”: 10}

  • The JSON object it takes a list of key/value pairs and returns a JSON object containing those pairs.

 

obj.png

String :

  • A string is a sequence of zero or more unicode characters, wrapped on double quotes , using backslash escapes.
  • The strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation.
  • This character set are converted to utf8mb4 as necessary. For strings in the ascii or utf8 character sets, no conversion is needed because ascii and utf8 are subsets of utf8mb4.

 

String.png

Array (1).jpg

String

JSON Document Validation :

  • The JSON documents can be stored in a JSON column, so you get automatic validation of your data. If you try to store an invalid JSON document in a JSON column, you will get an error.

 

Doc_1.png

 

  • The most important thing is JSON column cannot not stored as a plain text value.

Doc_2.png

  •  It is stored in an optimized binary format that allows for quicker access to object members and array elements.

Doc_3.png

  •  The lookup table has pointers to every key/value pair in the JSON document, sorted on the key.
  • It allows the json_extract function to perform a binary search for the ‘name’ key in the table and read the corresponding value directly.

JSON Merge :

  • JSON merge() it combines a two or more json documents and produce a single result.

Example :

Merge_1.png

  •  When a string is parsed and found to be a valid JSON document, it is also normalized.
  • If you compare the two JSON documents and it’s find a duplicate  key found , The  earlier document is discarded.

Example :

  • If multiple objects have the same key, the value for that key in the resulting merged object is an array containing single key values.

 

Merge_2.png

JSON User defined variables :

  • The user defined variables is not a JSON datatypes,here i use @j variable its looks like a JSON value and has the same character set and collation as a JSON value, it does not have the JSON data type.
  • In the result JSON object() is converted to a string when assigned to the variable.

Example :

user_var.png

  • Strings is converting JSON values have a character set of utf8mb4 and a collation of utf8mb4_bin.

user_var_1.png

  • The utf8mb4_bin is a binary collation, you just compare to JSON value.The JSON  is  a case  sensitive.
  • This case sensitivity also applies to the JSON null, true, and false literals, which always must be written in lowercase.

Example :

user_var_2.png

  • The case sensitivity of the JSON literals differs from that of the SQL null, true, and false literals, It can be written in any lettercase.

user_var_3.png

Searching JSON values :

  • The path expressions are useful to find the extract parts of or modify a JSON document, to specify where within that document to operate.

Example :

search_1.png

  • The key name must be specified within double quotation marks if the name without quotes is not legal within path expressions.
  • The Path contain * or ** wildcards.
  • .[*] evaluates to the values of all members in a JSON object.
  • [*] evaluates to the values of all elements in a JSON array.
  • prefix**suffix evaluates to all paths that begin with the named prefix and end with the named suffix.

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s