cockroachdb / cockroachdb/cockroach

sql/json: support for JSON_TABLE

Open
#131,609 0 comments 0 reactions 0 assignees View on GitHub
A-sql-json C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Postgres 17 supports JSON_TABLE that can transform JSON into a table. We should consider adding it:
```sql
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_details JSONB
);

INSERT INTO orders (order_details) VALUES
('{
"items": [
{"item_id": 1, "name": "Laptop", "quantity": 2},
{"item_id": 2, "name": "Mouse", "quantity": 5}
]
}'),
('{
"items": [
{"item_id": 3, "name": "Keyboard", "quantity": 3},
{"item_id": 4, "name": "Monitor", "quantity": 1}
]
}');
```
Can be queried with
```sql
SELECT
order_id,
jt.item_id,
jt.name,
jt.quantity
FROM
orders,
JSON_TABLE(
order_details,
'$.items[*]'
COLUMNS (
item_id INT PATH '$.item_id',
name TEXT PATH '$.name',
quantity INT PATH '$.quantity'
)
) AS jt;
```
Which returns:
```
order_id | item_id | name | quantity
----------+---------+----------+----------
1 | 1 | Laptop | 2
1 | 2 | Mouse | 5
2 | 3 | Keyboard | 3
2 | 4 | Monitor | 1
(4 rows)
```
https://www.postgresql.org/docs/17/functions-json.html#FUNCTIONS-SQLJSON-TABLE

Jira issue: CRDB-42631

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.