JSON Lists modification

[OrangieLou]

What happens?

It's easy to access the ith item of a json list jl->>0. However, there are no possibilities to access larger parts of the list and/or modify them, e.g. remove the first element.

There is a general lack of functions working with json in Duckdb.

To Reproduce

-- access ith element
WITH a(b) AS (
VALUES (json_array(1, 2, 3, 4, 5, 6)), (json_array(4,5,6,7, 8, 9, 10, 11)), (json_array(8, 9, 10, 11, 12))
)
SELECT b->>1 FROM a;

-- all indices have to be mentioned separately (no [1:5] etc)
WITH a(b) AS (
VALUES (json_array(1, 2, 3, 4, 5, 6)), (json_array(4,5,6,7, 8, 9, 10, 11)), (json_array(8, 9, 10, 11, 12))
)
SELECT b->[1,2,3,4] FROM a;

-- Error: Invalid Input Error: List of paths must be constant
WITH a(b) AS (
VALUES (json_array(1, 2, 3, 4, 5, 6)), (json_array(4,5,6,7, 8, 9, 10, 11)), (json_array(8, 9, 10, 11, 12))
)
SELECT b->range(1, CAST(json_array_length(b) AS BIGINT)) FROM a;

OS:

macOS 13.2.1 (M1)

DuckDB Version:

master as of 23/3/23

DuckDB Client:

command line

Full Name:

Louisa Lambrecht

Affiliation:

U Tübingen

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree