dolthub / dolthub/doltgresql

json values cannot be persisted inside arrays or composite types

Open
#3,209 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

A `json` value cannot be persisted inside an array or a composite type. Both fail on INSERT with the same error, while the `jsonb` equivalents work:

```
ERROR: "json" serialization requires a string argument, got types.JSONDocument (SQLSTATE XX000)
```

```sql
CREATE TABLE ja (id int PRIMARY KEY, j json[]);
INSERT INTO ja VALUES (1, ARRAY['{"a":1}'::json]); -- ERROR
INSERT INTO ja VALUES (1, '{"{\"a\": 1}"}'); -- ERROR (array literal form fails too)

CREATE TYPE jrec AS (id int, j json);
CREATE TABLE jc (id int PRIMARY KEY, r jrec);
INSERT INTO jc VALUES (1, ROW(1, '{"a":1}')::jrec); -- ERROR

-- postgres accepts all three
```

It is specific to persistence — building the same values in an expression works, so the failure is in writing an element rather than in constructing it:

```sql
SELECT ARRAY['{"a":1}'::json]; -- works: {"{\"a\": 1}"}
```

And it is specific to `json`; the `jsonb` versions round-trip fine:

```sql
CREATE TABLE jba (id int PRIMARY KEY, j jsonb[]);
INSERT INTO jba VALUES (1, ARRAY['{"a":1}'::jsonb, '{"b":2}'::jsonb]);
SELECT j FROM jba; -- {"{\"a\": 1}","{\"b\": 2}"}

CREATE TYPE jbrec AS (id int, j jsonb);
CREATE TABLE jbc (id int PRIMARY KEY, r jbrec);
INSERT INTO jbc VALUES (1, ROW(1, '{"a":1}')::jbrec);
SELECT r FROM jbc; -- (1,"{\"a\": 1}")
```

Since nothing can currently be written through this path, there is no existing data in any format to stay compatible with.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the array and composite INSERT examples, then trace the persistence path for json values and compare it with the working jsonb path. Done means json values can be inserted into arrays and composite types and round-trip successfully, including the shown SQL forms.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.