Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: support sql arrays of JSONB #23468

Open
justinj opened this issue Mar 6, 2018 · 3 comments
Open

sql: support sql arrays of JSONB #23468

justinj opened this issue Mar 6, 2018 · 3 comments
Labels
A-sql-datatypes SQL column types usable in table descriptors. A-sql-json JSON handling in SQL. A-tools-postgrest A-tools-prisma C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) X-anchored-telemetry The issue number is anchored by telemetry references.

Comments

@justinj
Copy link
Contributor

justinj commented Mar 6, 2018 •
edited by rafiss
Loading

Some discussion of this feature here.

We skipped over this for the time being since it caused some headaches with escaping.

knz edit (2019-03-13): the bigger problem is that arrays of jsonb do not have a proper lexicographic encoding. This can't be addressed properly until the sql/kv mapping is extended to support non-lexicographic orderings and lookups.

Jira issue: CRDB-5816
@justinj justinj added this to the 2.1 milestone Mar 6, 2018
@jordanlewis jordanlewis added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-json JSON handling in SQL. labels May 2, 2018
@knz knz added the A-sql-datatypes SQL column types usable in table descriptors. label May 14, 2018
@knz knz modified the milestones: 2.1, 2.2 Aug 30, 2018
@petermattis petermattis removed this from the 2.2 milestone Oct 5, 2018
@knz knz added the X-anchored-telemetry The issue number is anchored by telemetry references. label Mar 13, 2019
@knz knz mentioned this issue Mar 13, 2019
Merged
knz added a commit to knz/cockroach that referenced this issue Mar 14, 2019
@knz
sql: add proper telemetry for arrays of JSON
c04ff44 
Arrays of JSON are not supported but the errors did not
link attempted uses to issue cockroachdb#23468. This patch fixes it.

Release note: None
knz added a commit to knz/cockroach that referenced this issue Mar 14, 2019
@knz
sql: add proper telemetry for arrays of JSON
f01a4ba 
Arrays of JSON are not supported but the errors did not
link attempted uses to issue cockroachdb#23468. This patch fixes it.

Release note: None
jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 2, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
cebc811 
The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: cockroachdb#5807   (sql: Add support for TEMP tables)
 151: cockroachdb#17511  (sql: support stored procedures)
  86: cockroachdb#26097  (sql: make TIMETZ more pg-compatible)
  56: cockroachdb#10735  (sql: support SQL savepoints)
  55: cockroachdb#32552  (multi-dim arrays)
  55: cockroachdb#26508  (sql: restricted DDL / DML inside transactions)
  52: cockroachdb#32565  (sql: support optional TIME precision)
  39: cockroachdb#243    (roadmap: Blob storage)
  33: cockroachdb#26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: cockroachdb#27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: cockroachdb#12123  (sql: Can't drop and replace a table within a transaction)
  24: cockroachdb#26443  (sql: support user-defined schemas between database and table)
  20: cockroachdb#21286  (sql: Add support for geometric types)
  18: cockroachdb#6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: cockroachdb#22329  (Support XA distributed transactions in CockroachDB)
  16: cockroachdb#24062  (sql: 32 bit SERIAL type)
  16: cockroachdb#30352  (roadmap:when CockroachDB  will support cursor?)
  12: cockroachdb#27791  (sql: support RANGE types)
   8: cockroachdb#40195  (pgwire: multiple active result sets (portals) not supported)
   8: cockroachdb#6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: cockroachdb#23468  (sql: support sql arrays of JSONB)
   5: cockroachdb#40854  (sql: set application_name from connection string)
   4: cockroachdb#35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: cockroachdb#32610  (sql: can't insert self reference)
   4: cockroachdb#40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: cockroachdb#35897  (sql: unknown function: pg_terminate_backend())
   4: cockroachdb#4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: cockroachdb#27796  (sql: support user-defined DOMAIN types)
   3: cockroachdb#3781   (sql: Add Data Type Formatting Functions)
   3: cockroachdb#40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: cockroachdb#35882  (sql: support other character sets)
   2: cockroachdb#10028  (sql: Support view queries with star expansions)
   2: cockroachdb#35807  (sql: INTERVAL output doesn't match PG)
   2: cockroachdb#35902  (sql: large object support)
   2: cockroachdb#40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: cockroachdb#18846  (sql: Support CIDR column type)
   1: cockroachdb#9682   (sql: implement computed indexes)
   1: cockroachdb#31632  (sql: FK options (deferrable, etc))
   1: cockroachdb#24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: cockroachdb#36215  (sql: enable setting standard_conforming_strings to off)
   1: cockroachdb#32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: cockroachdb#36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: cockroachdb#26732  (sql: support the binary operator: <int> / <float>)
   1: cockroachdb#23299  (sql: support coercing string literals to arrays)
   1: cockroachdb#36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: cockroachdb#26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: cockroachdb#21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: cockroachdb#36179  (sql: implicity convert date to timestamp)
   1: cockroachdb#36118  (sql: Cannot parse '24:00' as type time)
   1: cockroachdb#31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None
jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 24, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
4010eac 
The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: cockroachdb#5807   (sql: Add support for TEMP tables)
 151: cockroachdb#17511  (sql: support stored procedures)
  86: cockroachdb#26097  (sql: make TIMETZ more pg-compatible)
  56: cockroachdb#10735  (sql: support SQL savepoints)
  55: cockroachdb#32552  (multi-dim arrays)
  55: cockroachdb#26508  (sql: restricted DDL / DML inside transactions)
  52: cockroachdb#32565  (sql: support optional TIME precision)
  39: cockroachdb#243    (roadmap: Blob storage)
  33: cockroachdb#26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: cockroachdb#27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: cockroachdb#12123  (sql: Can't drop and replace a table within a transaction)
  24: cockroachdb#26443  (sql: support user-defined schemas between database and table)
  20: cockroachdb#21286  (sql: Add support for geometric types)
  18: cockroachdb#6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: cockroachdb#22329  (Support XA distributed transactions in CockroachDB)
  16: cockroachdb#24062  (sql: 32 bit SERIAL type)
  16: cockroachdb#30352  (roadmap:when CockroachDB  will support cursor?)
  12: cockroachdb#27791  (sql: support RANGE types)
   8: cockroachdb#40195  (pgwire: multiple active result sets (portals) not supported)
   8: cockroachdb#6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: cockroachdb#23468  (sql: support sql arrays of JSONB)
   5: cockroachdb#40854  (sql: set application_name from connection string)
   4: cockroachdb#35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: cockroachdb#32610  (sql: can't insert self reference)
   4: cockroachdb#40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: cockroachdb#35897  (sql: unknown function: pg_terminate_backend())
   4: cockroachdb#4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: cockroachdb#27796  (sql: support user-defined DOMAIN types)
   3: cockroachdb#3781   (sql: Add Data Type Formatting Functions)
   3: cockroachdb#40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: cockroachdb#35882  (sql: support other character sets)
   2: cockroachdb#10028  (sql: Support view queries with star expansions)
   2: cockroachdb#35807  (sql: INTERVAL output doesn't match PG)
   2: cockroachdb#35902  (sql: large object support)
   2: cockroachdb#40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: cockroachdb#18846  (sql: Support CIDR column type)
   1: cockroachdb#9682   (sql: implement computed indexes)
   1: cockroachdb#31632  (sql: FK options (deferrable, etc))
   1: cockroachdb#24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: cockroachdb#36215  (sql: enable setting standard_conforming_strings to off)
   1: cockroachdb#32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: cockroachdb#36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: cockroachdb#26732  (sql: support the binary operator: <int> / <float>)
   1: cockroachdb#23299  (sql: support coercing string literals to arrays)
   1: cockroachdb#36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: cockroachdb#26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: cockroachdb#21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: cockroachdb#36179  (sql: implicity convert date to timestamp)
   1: cockroachdb#36118  (sql: Cannot parse '24:00' as type time)
   1: cockroachdb#31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None
craig bot pushed a commit that referenced this issue Nov 7, 2019
@jordanlewis
Merge #41252
27fd417 
41252: roachtest: add test that aggregates orm blacklist failures r=jordanlewis a=jordanlewis

The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep
up to date. If we write down blacklists in code, then we can use an approach
like this to always have an up to date aggregation.

So far it seems like there's just a lot of unknowns to categorize still.

The output today:

```
=== RUN   TestBlacklists
 648: unknown                                                (unknown)
 493: #5807   (sql: Add support for TEMP tables)
 151: #17511  (sql: support stored procedures)
  86: #26097  (sql: make TIMETZ more pg-compatible)
  56: #10735  (sql: support SQL savepoints)
  55: #32552  (multi-dim arrays)
  55: #26508  (sql: restricted DDL / DML inside transactions)
  52: #32565  (sql: support optional TIME precision)
  39: #243    (roadmap: Blob storage)
  33: #26725  (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea))
  31: #27793  (sql: support custom/user-defined base scalar (primitive) types)
  24: #12123  (sql: Can't drop and replace a table within a transaction)
  24: #26443  (sql: support user-defined schemas between database and table)
  20: #21286  (sql: Add support for geometric types)
  18: #6583   (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}))
  17: #22329  (Support XA distributed transactions in CockroachDB)
  16: #24062  (sql: 32 bit SERIAL type)
  16: #30352  (roadmap:when CockroachDB  will support cursor?)
  12: #27791  (sql: support RANGE types)
   8: #40195  (pgwire: multiple active result sets (portals) not supported)
   8: #6130   (sql: add support for key watches with notifications of changes)
   5: Expected Failure                                       (unknown)
   5: #23468  (sql: support sql arrays of JSONB)
   5: #40854  (sql: set application_name from connection string)
   4: #35879  (sql: `default_transaction_read_only` should also accept 'on' and 'off')
   4: #32610  (sql: can't insert self reference)
   4: #40205  (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE)
   4: #35897  (sql: unknown function: pg_terminate_backend())
   4: #4035   (sql/pgwire: missing support for row count limits in pgwire)
   3: #27796  (sql: support user-defined DOMAIN types)
   3: #3781   (sql: Add Data Type Formatting Functions)
   3: #40476  (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`)
   3: #35882  (sql: support other character sets)
   2: #10028  (sql: Support view queries with star expansions)
   2: #35807  (sql: INTERVAL output doesn't match PG)
   2: #35902  (sql: large object support)
   2: #40474  (sql: support `SELECT ... FOR UPDATE OF` syntax)
   1: #18846  (sql: Support CIDR column type)
   1: #9682   (sql: implement computed indexes)
   1: #31632  (sql: FK options (deferrable, etc))
   1: #24897  (sql: CREATE OR REPLACE VIEW)
   1: pass?                                                  (unknown)
   1: #36215  (sql: enable setting standard_conforming_strings to off)
   1: #32562  (sql: support SET LOCAL and txn-scoped session variable changes)
   1: #36116  (sql: psychopg: investigate how `'infinity'::timestamp` is presented)
   1: #26732  (sql: support the binary operator: <int> / <float>)
   1: #23299  (sql: support coercing string literals to arrays)
   1: #36115  (sql: psychopg: investigate if datetimetz is being returned instead of datetime)
   1: #26925  (sql: make the CockroachDB integer types more compatible with postgres)
   1: #21085  (sql: WITH RECURSIVE (recursive common table expressions))
   1: #36179  (sql: implicity convert date to timestamp)
   1: #36118  (sql: Cannot parse '24:00' as type time)
   1: #31708  (sql: support current_time)
```

Release justification: non-production change
Release note: None

Co-authored-by: Jordan Lewis <jordanthelewis@gmail.com>
@dpkirchner
Copy link

Until this is complete, I think it'd be worth updating the documentation to show that JSON arrays are not yet supported:

https://www.cockroachlabs.com/docs/stable/data-types.html

"ARRAY: A 1-dimensional, 1-indexed, homogeneous array of any non-array data type."

@rohany rohany mentioned this issue Apr 26, 2020
Merged
@rafiss rafiss mentioned this issue Jan 26, 2021
Closed
@nvanbenschoten nvanbenschoten mentioned this issue Sep 8, 2021
Closed
22 tasks
@rafiss rafiss mentioned this issue Sep 10, 2021
Merged
craig bot pushed a commit that referenced this issue Sep 13, 2021
@jordanlewis
Merge #70041
394e35c 
70041: sql: support in-memory SQL arrays of JSON r=jordanlewis a=jordanlewis

refs #23468
refs #69010

This commit adds support for SQL arrays of JSON in memory. This allows
functions like `array_agg` to work against JSON objects, which is useful
for compatibility.

Notably, this commit does not add support for storing JSON arrays in
table columns.

Release note (sql change): add support for SQL arrays containing JSON
for in-memory processing. This does not add support for storing SQL
arrays of JSON in tables.

Co-authored-by: Jordan Lewis <jordanthelewis@gmail.com>
@exalate-issue-sync exalate-issue-sync bot added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label Oct 4, 2021
@maryliag maryliag mentioned this issue Jan 26, 2022
Merged
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 7, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
433a23b 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 7, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
775e865 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 7, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
58e65df 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 7, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
e13003b 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 7, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
b695457 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 8, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
94cb7b9 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 8, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
5c0d8d9 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jun 16, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
9b0927c 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jul 4, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
af3edc5 
Not supported by CRDB: cockroachdb/cockroach#23468
dvarrazzo added a commit to psycopg/psycopg that referenced this issue Jul 12, 2022
@dvarrazzo
test(crdb): skip tests with arrays of json[b]
98950f0 
Not supported by CRDB: cockroachdb/cockroach#23468
@rafiss rafiss mentioned this issue Oct 28, 2022
Closed
@l0g1x
Copy link

l0g1x commented Feb 6, 2023

Any update on when JSON will be supported? Im trying to switch from railway to cockroach serverless (Postgres) and am getting the following error when trying to apply a migration

Applying migration `20230206223926_init`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20230206223926_init

Database error code: 0A000

Database error:
ERROR: unimplemented: arrays of JSON unsupported as column type
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/23468/v22.2

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E0A000), message: "unimplemented: arrays of JSON unsupported as column type", detail: None, hint: Some("You have attempted to use a feature that is not yet implemented.\nSee: https://go.crdb.dev/issue-v/23468/v22.2"), position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("col_type_info.go"), line: Some(97), routine: Some("ValidateColumnDefType") }

@Just-Moh-it
Copy link

Any alternatives to JSON we could use for similar data... I could simply make a column string and JSON.stringify() the results, but there must be a better way, right?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-datatypes SQL column types usable in table descriptors. A-sql-json JSON handling in SQL. A-tools-postgrest A-tools-prisma C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) X-anchored-telemetry The issue number is anchored by telemetry references.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
9 participants
@jordanlewis @dpkirchner @justinj @knz @rafiss @otan @l0g1x @petermattis @Just-Moh-it