cockroachdb / cockroachdb/cockroach
sql/json: support for JSON_TABLE
- 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
Assessment
This issue has not been assessed yet.