website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

JSON_EXTRACT_PATH

9min

In this article, you will learn about the JSON_EXTRACT_PATH() function to extract the JSON sub-object.

Overview

JSON_EXTRACT_PATH() function extracts JSON nested value from a specified path.

Syntax

The syntax of the JSON_EXTRACT_PATH() function can be seen below. 

Syntax
|
JSON_EXTRACT_PATH(from_json JSON, path TEXT[])

  • from_json: the JSON value from which 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:

Syntax
|
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.

Examples

These examples display how JSON_EXTRACT_PATH() extracts the "oxla" JSON sub-object from the specified path.

1) Use the below query:

Json extract path
|
SELECT JSON_EXTRACT_PATH('{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}', 'f4', 'f6');


or

Another option
|
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"oxla"}}'::JSON -> 'f4' -> 'f6';


The query above will return the following result.

Output
|
+---------+
| f       |
+---------+
| "oxla"  |
+---------+


2) Run the query below:

Json extract path
|
SELECT
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "subtract", "y": "plus"}}', 'b', 'x') AS "bx",
    JSON_EXTRACT_PATH('{"a": 1, "b": {"x": "multiply", "y": "divide"}}', 'b', 'y') AS "by";


You will get the following output:

Output
|
+---------------+-------------+
| bx            | by          |
+---------------+-------------+
| "subtract"    | "divide"    |
+---------------+-------------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
JSON Functions
NEXT
JSON_EXTRACT_PATH_TEXT
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Syntax
Another Option
Examples

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.




©2022 Oxla