JSON_EXTRACT_PATH_TEXT

8min

Overview
The JSON_EXTRACT_PATH_TEXT() function extracts JSON nested value from a specified JSON value according to the defined path. 
This function may be similar to the JSON_EXTRACT_PATH(). This function returns a value of type string instead of type JSON.
Syntax
The JSON_EXTRACT_PATH_TEXT() syntax is shown below: 
Syntax
|
JSON_EXTRACT_PATH_TEXT(from_json JSON, path TEXT[])
JSON_EXTRACT_PATH_TEXT(from_json JSON, path TEXT[])
﻿
The required arguments are explained below.
from_json: the JSON value to extract.
path: the path to extract.
Another Option
Besides the syntax above, Oxla provides and supports the use of operators in queries. See the syntax below:
Another option
|
SELECT 'from_json'::JSON ->> 'path';
SELECT 'from_json'::JSON ->> 'path';
﻿
from_json: the JSON value from which to extract.
::JSON: a symbol that casts the string literal to a JSON type.
path: key of the field that we want to extract.
Example
1) This example shows how to use the JSON_EXTRACT_PATH_TEXT() function to extract values ​​from a JSON object at a specified index. 
Run the following query:
Json extract path
|
SELECT JSON_EXTRACT_PATH_TEXT('{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}', 'a') AS "result a";
SELECT JSON_EXTRACT_PATH_TEXT('{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}', 'a') AS "result a";
﻿
or
Another option
|
SELECT '{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}'::JSON ->> 'a' AS "result a";
SELECT '{"a": "Oxla", "b": {"x": 1.234, "y": 4.321}}'::JSON ->> 'a' AS "result a";
﻿
2) The JSON_EXTRACT_PATH_TEXT() function extracts the values and returns the output below:
Output
|
+------------+
| result a   |
+------------+
| "Oxla"     |
+------------+
+------------+
| result a   |
+------------+
| "Oxla"     |
+------------+
﻿
﻿

Updated 31 Aug 2023

Did this page help you?

TABLE OF CONTENTS