search

Cara menggunakan mysql json column size

1 years ago
Komentar: 0
Dibaca: 102

In MySQL, the 

SELECT JSON_STORAGE_SIZE('{"a": 1}') Result;
1 function returns the number of bytes used to store the binary representation of a JSON document.

Show

You provide the JSON document as an argument.

When the argument is a 

SELECT JSON_STORAGE_SIZE('{"a": 1}') Result;
2 column, this function returns the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.

When the argument is a string, the function returns the amount of storage space in the JSON binary representation that is created by parsing the string as JSON and converting it to binary.

Syntax

The syntax goes like this:

JSON_STORAGE_SIZE(json_val)

Where 

SELECT JSON_STORAGE_SIZE('{"a": 1}') Result;
3 is the JSON document for which to return the storage size. This must be a valid JSON document or a string which can be parsed as one.

Example

Left-pad the string with "ABC", to a total length of 20:

SELECT LPAD("SQL Tutorial", 20, "ABC");

Try it Yourself »

Definition and Usage

The LPAD() function left-pads a string with another string, to a certain length.

Note: Also look at the RPAD() function.

Syntax

LPAD(string, length, lpad_string)

Parameter Values

ParameterDescriptionstringRequired. The original string. If the length of the original string is larger than the length parameter, this function removes the overfloating characters from stringlengthRequired. The length of the string after it has been left-paddedlpad_stringRequired. The string to left-pad to string

Technical Details

More Examples

Example

Left-pad the text in "CustomerName" with "ABC", to a total length of 30:

SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName
FROM Customers;

Try it Yourself »

Casting from 

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
8, 
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
9, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
0, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
1, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
2, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
3, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
4 or 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
5 is supported. Casting from 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
6, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
7 or 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
8 is supported when the element type of the array is one of the supported types, or when the key type of the map is 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
5 and value type of the map is one of the supported types, or when every field type of the row is one of the supported types. Behaviors of the casts are shown with the examples below:

SELECT CAST(NULL AS JSON); -- NULL
SELECT CAST(1 AS JSON); -- JSON '1'
SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON); -- JSON '"abc"'
SELECT CAST(true AS JSON); -- JSON 'true'
SELECT CAST(1.234 AS JSON); -- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON); -- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON); -- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON); -- JSON '[[1,23],[456]]'
SELECT CAST(MAP_FROM_ENTRIES(ARRAY[('k1', 1), ('k2', 23), ('k3', 456)]) AS JSON); -- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON); -- JSON '[123,"abc",true]'

Note

Casting from NULL to 

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
0 is not straightforward. Casting from a standalone 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
1 will produce a SQL 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
1 instead of 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
3. However, when casting from arrays or map containing 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
1s, the produced 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
0 will have 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
6s in it.

Note

When casting from 

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
8 to 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
0, the result is a JSON array rather than a JSON object. This is because positions are more important than names for rows in SQL.

Cast from JSON

Casting to 

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
8, 
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
9, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
0, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
1, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
2, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
3, 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
4 or 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
5 is supported. Casting to 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
6 and 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
7 is supported when the element type of the array is one of the supported types, or when the key type of the map is 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
5 and value type of the map is one of the supported types. Behaviors of the casts are shown with the examples below:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}

Note

JSON arrays can have mixed element types and JSON maps can have mixed value types. This makes it impossible to cast them to SQL arrays and maps in some cases. To address this, Presto supports partial casting of arrays and maps:

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']

Note

When casting from 

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
0 to 
SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']
8, both JSON array and JSON object are supported.

JSON Functions

is_json_scalar(json) → boolean

Determine if 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
2 is a scalar (i.e. a JSON number, a JSON string, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
3, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
4 or 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
6):

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false

json_array_contains(json, value) → boolean

Determine if 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
6 exists in 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
2 (a string containing a JSON array):

SELECT json_array_contains('[1, 2, 3]', 2);

json_array_get(json_array, index) → json

Warning

The semantics of this function are broken. If the extracted element is a string, it will be converted into an invalid 

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
0 value that is not properly quoted (the value will not be surrounded by quotes and any interior quotes will not be escaped).

We recommend against using this function. It cannot be fixed without impacting existing usages and may be removed in a future release.

Returns the element at the specified index into the 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
9. The index is zero-based:

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'

This function also supports negative indexes for fetching element indexed from the end of an array:

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'

If the element at the specified index doesn’t exist, the function returns null:

SELECT json_array_get('[]', 0); -- null
SELECT json_array_get('["a", "b", "c"]', 10); -- null
SELECT json_array_get('["c", "b", "a"]', -10); -- null

json_array_length(json) → bigint

Returns the array length of 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
2 (a string containing a JSON array):

SELECT json_array_length('[1, 2, 3]');

json_extract(json, json_path) → json

Evaluates the JSONPath-like expression 

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
1 on 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
2 (a string containing JSON) and returns the result as a JSON string:

SELECT json_extract(json, '$.store.book');

json_extract_scalar(json, json_path) → varchar

Like , but returns the result value as a string (as opposed to being encoded as JSON). The value referenced by 

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
1 must be a scalar (boolean, number or string):

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
0

json_format(json) → varchar

Returns the JSON text serialized from the input JSON value. This is inverse function to :

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
1

Note

and 

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
7 have completely different semantics.

serializes the input JSON value to JSON text conforming to RFC 7159. The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number, 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
3, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
4 or 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
6:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
2

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
7 casts the JSON value to the corresponding SQL VARCHAR value. For JSON string, JSON number, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
3, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
4 or 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
6, the cast behavior is same as the corresponding SQL type. JSON object and JSON array cannot be cast to VARCHAR:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
3

json_parse(string) → json

Returns the JSON value deserialized from the input JSON text. This is inverse function to :

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
4

Note

and 

SELECT json_array_get('[]', 0); -- null
SELECT json_array_get('["a", "b", "c"]', 10); -- null
SELECT json_array_get('["c", "b", "a"]', -10); -- null
8 have completely different semantics.

expects a JSON text conforming to RFC 7159, and returns the JSON value deserialized from the JSON text. The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number, 

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
3, 
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
4 or 
SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false
6:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
5

SELECT json_array_get('[]', 0); -- null
SELECT json_array_get('["a", "b", "c"]', 10); -- null
SELECT json_array_get('["c", "b", "a"]', -10); -- null
8 takes any VARCHAR value as input, and returns a JSON string with its value set to input string:

SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}
6

json_size(json, json_path) → bigint

Like , but returns the size of the value. For objects or arrays, the size is the number of members, and the size of a scalar value is zero:

kode mysql

Pos Terkait

Kolom menghilang di lembar google
Kolom menghilang di lembar google

Cara menggunakan sending email in php
Cara menggunakan sending email in php

Persimpangan daftar daftar python
Persimpangan daftar daftar python

Bagaimana cara menambahkan footer ke halaman wordpress?
Bagaimana cara menambahkan footer ke halaman wordpress?

Cara menggunakan what are html5 games
Cara menggunakan what are html5 games

Dapatkah Anda menambahkan array dalam javascript?
Dapatkah Anda menambahkan array dalam javascript?

Cara menggunakan menggabungkan html dan python
Cara menggunakan menggabungkan html dan python

Mengonversi objek menjadi array python
Mengonversi objek menjadi array python

Bisakah kita menyimpan daftar di mysql?
Bisakah kita menyimpan daftar di mysql?

Apa itu mysql dan korelasinya dengan dbms?
Apa itu mysql dan korelasinya dengan dbms?

Periklanan

BERITA TERKINI

Apakah fungsi data cleaning dalam python?
1 years ago . by UndiscoveredDucking
Cara cek kartu kredit bca aktif atau tidak
1 years ago . by MythicalCoverage
Cara menggunakan setcookie not working javascript
1 years ago . by DrabServitude
Cara menggunakan fungsi struktur html
1 years ago . by ParticipatingAdvert
Which MySQL edition used for physical backup?
1 years ago . by HeedlessLikeness
Php imagemagick pdf ke png kualitas tinggi
1 years ago . by QuietRegularity
Cara menggunakan python raise typeerror
1 years ago . by DifficultImmunization
Cara mengisi game di ps 3
1 years ago . by VeterinaryReviewer
Bagaimana Anda memeriksa apakah saya sudah menginstal mysql?
1 years ago . by PriestlyDonkey
Mengapa gambar tidak ditampilkan di html?
1 years ago . by High-qualityWidget

Toplist Popular

#1
Top 7 os elementos da paisagem descritos no texto correspondem a aspectos biogeográficos presentes na 2022
1 years ago
#2
Top 6 a energia nuclear é uma alternativa aos combustíveis fósseis que, se não gerenciada de forma correta 2022
1 years ago
#3
Top 6 a persistência das reivindicações relativas à aplicação desse preceito normativo tem em vista 2022
1 years ago
#4
Top 7 wie viele zigaretten sind ein shisha kopf 2022
2 years ago
#5
Top 7 wie viel kostet eine badewanne voll wasser 2022
2 years ago
#6
Top 8 golf 7 standheizung fernbedienung anlernen 2022
2 years ago
#7
Top 8 sky receiver aufnehmen nicht möglich ohne abo 2022
2 years ago
#8
Top 5 einhell kgsz 300 ug sägeblatt wechseln 2022
2 years ago
#9
Top 7 renault clio 3 abblendlicht wechseln anleitung 2022
2 years ago
#10
Top 8 outlook diese datendatei ist mit einem e-mail-konto verbunden 2022
2 years ago

Periklanan

Terpopuler

Periklanan

Tentang Kami

Legal

Dukungan

Social

homeentrdeptjp
Copyright © 2024 membukakan Inc.