JSON Parse & Unnest

Trino Documentation

Important Functions:

• json_parse:

  • Converts a raw JSON string into a SQL-readable structure—specifically an ARRAY(MAP(VARCHAR, JSON)) when the input is a JSON array of objects.

• CROSS JOIN UNNEST:

  • Flattens JSON arrays by turning each array element into its own row.

  • Used twice:

    • First, to unnest the array of user objects.

    • Second, to unnest the nested siblings array within each user.

• json_extract_scalar:

  • Extracts scalar values (e.g., strings, numbers) from JSON fields.

  • Used to access:

    • A user’s username directly from the JSON map.

    • A nested value like contactdetails.phonenumber.

    • Each sibling’s firstname.

Sub Query Steps:

• Step 1 (data): Load the raw JSON string into a table.

• Step 2 (Parsed): Parse the JSON string into an array of JSON maps.

• Step 3 (Unnested): Unnest the array so each user object becomes its own row; extract values using json_extract_scalar.

• Step 4 (Unnest Siblings): Unnest the siblings array within each user; extract each sibling's firstname.

EXAMPLE QUERY (Can be copy/pasted in SQL Box):

WITH "data" AS ( SELECT '[{"id":1,"username":"jrodriguez","contactdetails":{"phonenumber":"555-123-4567"},"siblings":[{"firstname":"Maria"},{"firstname":"Luis"}]},{"id":2,"username":"asmith","contactdetails":{"phonenumber":"555-987-6543"},"siblings":[{"firstname":"Emily"}]}]' "jsondata" ), "Parsed" AS ( SELECT CAST( "json_parse" ("data"."jsondata") AS ARRAY(MAP(VARCHAR, JSON)) ) "parsed_array" FROM "data" ), "Unnested" AS ( SELECT "Unnest" ['id'] "id", "Unnest" ['username'] "username", "json_extract_scalar" ("Unnest" ['username'], '$') "username2", "json_extract_scalar" ("Unnest" ['contactdetails'], '$.phonenumber') "phonenumber", CAST( "Unnest" ['siblings'] AS ARRAY(MAP(VARCHAR, JSON)) ) "siblings" FROM ( "Parsed" CROSS JOIN UNNEST ("parsed_array") "a" ("Unnest") ) ), "Unnest Siblings" AS ( SELECT "Unnested"."id", "Unnested"."username2", "Unnested"."phonenumber", "json_extract_scalar" ("sibling" ['firstname'], '$') "firstname" FROM ( "Unnested" CROSS JOIN UNNEST ("siblings") "a" ("sibling") ) ) SELECT * FROM "Unnest Siblings"