JSON_PATH_QUERY_ARRAY

Learn how to use the JSON_PATH_QUERY_ARRAY semi-structured data function in PlaidCloud Lakehouse. Get all JSON items returned by JSON path for the specified...

Get all JSON items returned by JSON path for the specified JSON value and wrap a result into an array.

Analyze Syntax

func.json_path_query_array(<variant>, <path_name>)

Analyze Example

table.name, func.json_path_query_array(table.details, '$.features.*').alias('all_features')

   name     |     all_features
------------+-----------------------
 Laptop     | ["16GB", "512GB"]
 Smartphone | ["4GB", "128GB"]
 Headphones | ["20h", "5.0"]

SQL Syntax

JSON_PATH_QUERY_ARRAY(<variant>, '<path_name>')

Return Type

VARIANT

SQL Examples

Create a Table and Insert Sample Data

CREATE TABLE products (
    name VARCHAR,
    details VARIANT
);

INSERT INTO products (name, details)
VALUES ('Laptop', '{"brand": "Dell", "colors": ["Black", "Silver"], "price": 1200, "features": {"ram": "16GB", "storage": "512GB"}}'),
       ('Smartphone', '{"brand": "Apple", "colors": ["White", "Black"], "price": 999, "features": {"ram": "4GB", "storage": "128GB"}}'),
       ('Headphones', '{"brand": "Sony", "colors": ["Black", "Blue", "Red"], "price": 150, "features": {"battery": "20h", "bluetooth": "5.0"}}');

Query Demo: Extracting All Features from Product Details as an Array

SELECT
    name,
    JSON_PATH_QUERY_ARRAY(details, '$.features.*') AS all_features
FROM
    products;

Result

   name    |         all_features
-----------+-----------------------
 Laptop    | ["16GB", "512GB"]
 Smartphone | ["4GB", "128GB"]
 Headphones | ["20h", "5.0"]