4

Finding and Getting Data

Simply having data in a MySQL JSON data type column is not much good by itself. Thankfully the engineers at MySQL have provided many extremely useful functions to get into the data, and this chapter delves into the functions for finding and retrieving that data.

All Keys

In Chapter 3, the JSON_KEYS() function was introduced. This function returns the top level of a JSON object as an array. Without the optional path, the function provides the top-level keys. With the optional path, it provides the top-level keys from that particular path.

Format: JSON_KEYS(json_doc[, path])

Example 4-1 JSON_KEYS with top level of JSON document

Example 4-2 JSON_KEYS with optional key to use as top level for reporting

NOTE   Refer to Examples 3-7 and 3-8 in Chapter 3 for specifying deeper-level keys and wildcard characters with a document.

Searching for a Key

Suppose you have a valid JSON document in a column or row in a table and you need to search for a certain key in that data. Or you need to search for every instance of a certain key in the data (such as a second phone number, additional e-mail addresses, and the like). JSON_CONTAINS_PATH uses the second argument, either ONE or ALL, to determine whether the key will be returned after finding only the first occurrence or finding all of the keys from the path provided as the third (and later) argument. In the next example, there is a match in the specific document to the geography key. JSON_CONTAINS_PATH() returns 1 (true) if the desired key is located in the document. In Example 4-3, the geography key is located, so 1 is returned. A 0 would be returned if the document/columns did not have the desired key.

Example 4-3 JSON_CONTAINS_PATH used to search for key geography

Searching for a Path

The various items in a JSON document have keys that are like stepping stones across a stream that let you navigate a path through the document. The key part of the key/value pair is a stepping stone to data. You can also search for multiple keys using a single JSON_CONTAINS_PATH statement. The following example is looking for both the geography and government keys. Both keys must be present for the server to return 1.

Example 4-4 Searching for geography and government keys in the data

The second argument in this function is either ONE or ALL. Use ONE when one key exists at least once in the path; use ALL when you want to find all keys present. In this example, a 0 is returned, which indicates that there is a geography key in the document/column but not a governmentx key. So the server reports no match.

Example 4-5 Failure using JSON_CONTAINS_PATH, indicated by the 0 because there is no governmentx key within the data

Searching for a Value

Use JSON_CONTAINS() to determine whether the value of specified key matches a specified value. This is an equivalency function: Does A equal B? In this example, the IndepYear does match the value 1776 for the document with the _id value equal to USA.

Format: JSON_CONTAINS(json_doc, val[, path])

Example 4-6 Using JSON_CONTAINS to determine whether the value of IndepYear of this record equals 1776

JSON_SEARCH() returns the position or key of a value. The preceding functions provided the value of given a key.

Example 4-7 Where is the United States in the path of this data?

This function will also check the full path and return matching keys. Note that this is searching on the value to return the key. The preceding examples were looking for the values given a key, while JSON_SEARCH returns keys given values. This function will provide the full path of the key for the given value.

Example 4-8 Where is North America in this JSON document? JSON_SEARCH provides a way to find the key given a value.