website logo
⌘K
🏠Homepage
πŸ‘‹Introduction
Key Concepts & Architecture
πŸš€Run Oxla in 2 minutes
πŸƒβ€β™‚οΈRun Oxla on S3
πŸ›«Multi-node Deployment
πŸ‘¨β€πŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
πŸ†šDifferences Between Oxla vs. PostgreSQL
πŸ“ˆPublic Metrics
Docs powered by
Archbee
SQL Reference
...
SQL Functions
JSON Functions

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[])
ο»Ώ

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';
ο»Ώ
  • 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";
ο»Ώ

or

Another option
|
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"     |
+------------+
ο»Ώ

ο»Ώ

Updated 31 Aug 2023
Did this page help you?
PREVIOUS
JSON_EXTRACT_PATH
NEXT
JSON_ARRAY_LENGTH
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Syntax
Another Option
Example
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




Β©2023 Oxla