Tracking: jsonb operations

[xiangjinwu]

Listing all jsonb operations from PostgreSQL, and which ones to implement now, soon, or until requested.

Basic

• TEXT format input / output (same as cast from / to string)
• BINARY format input / output

General for all types:

• IS [NOT] NULL / CASE WHEN / COALESCE / etc

Eq / Hash / Ord

• There is no plan to support this type of operations right now.
• Also includes IS [NOT] DISTINCT / NULLIF / IN / etc

Cast to simple types

• bool
• smallint / int / bigint / decimal / real / double precision
• There is no cast from them to jsonb, but a function to_jsonb.

Accessing array / object

• -> int / -> varchar: to access member as jsonb or cast to bool / number
• ->> int / ->> varchar: to access member as string. (Note that cast to string is DIFFERENT. See example below.)
• #> varchar[] / #>> varchar[] feat(expr): support #> and #>> operator for extracting jsonb at a path #13110
• jsonb_extract_path / jsonb_extract_path_text: feat(expr): support jsonb_extract_path(_text) function #13143

Informative / Debugging

• jsonb_array_length
• jsonb_pretty feat(expr): add jsonb_pretty function #13050
• jsonb_typeof

Predicate

• ? / ?| / ?&: object key lookup
  • TODO: decide what to do for array element lookup, which only works for string and may just be a side effect. See example below.
• @> / <@: containment feat(expr): support jsonb @>, <@, ?, ?| and ?& operator #13056

Construction / Mutation

• concat jsonb || jsonb → jsonb #12180
• - array index / - object key (also array string element) / - object keys / #- path feat(expr): support jsonb - and #- operator #13118
• jsonb_strip_nulls
• feat(expr): to_jsonb #12834: basis of many operations below.
• support jsonb_build_array / jsonb_build_object #13016
• jsonb_object: only when values are also strings. feat(expr): add jsonb_object function #13036

Subscripting

• Avoid j[index] / j[key] and use #> / #>> / -> / ->> instead.

Aggregation

• jsonb_agg feat(agg): support bool_and, bool_or, jsonb_agg, jsonb_object_agg #9452
• jsonb_object_agg

Table Function (aka Set Returning Function)

• jsonb_array_elements / jsonb_array_elements_text #11570
• jsonb_each / jsonb_each_text / jsonb_object_keys feat(expr): add jsonb table functions #9977

Advanced

• jsonb_populate_record / jsonb_populate_recordset / jsonb_to_record / jsonb_to_recordset
• jsonb_set / jsonb_set_lax feat(expr): implement jsonb_set function #17103
• jsonb_insert
• row_to_json / array_to_json

jsonpath type

Additional Details

While SQL array is 1-indexed, JSON array as part of SQL is still 0-indexed.

Our goal is to ingest JSON data and then extract strongly-typed columns from it. So operations on constructing / mutating / comparison of JSON are not of interest right now.

Dedicated string member access operator vs Cast to string

with t(v1) as (values (null::jsonb), ('null'), ('true'), ('1'), ('"a"'), ('[]'), ('{}')),
     j(v1) as (select ('{"k":' || v1::varchar || '}')::jsonb from t)
select
    v1 ->> 'k',
    (v1 -> 'k')::varchar,
    jsonb_typeof(v1 -> 'k')
from j order by 2;
----
a "a" string
1 1 number
[] [] array
NULL null null
true true boolean
{} {} object
NULL NULL NULL

Operations on object keys also work for array string elements
(TODO)

[xiangjinwu]

Removing the milestone as the remaining operators are optional / long-term.

[st1page]

jsonb_populate_record / jsonb_populate_recordset / jsonb_to_record / jsonb_to_recordset

Also, We might need another expression to optimize the ARRAY(jsonb_populate_recordset()) and ARRAY(jsonb_populate_recordset()) for user to fast cast from jsonb array to array of structs

[xiangjinwu]

Also, We might need another expression to optimize the ARRAY(jsonb_populate_recordset()) and ARRAY(jsonb_populate_recordset()) for user to fast cast from jsonb array to array of structs

Opened a dedicated issue: #17617

[xxchan]

What's still missing? Looks mostly finished

[xiangjinwu]

What's still missing? Looks mostly finished

PostgreSQL 16 (and maybe the upcoming 17) is still actively adding more JSON expressions. But I am okay with either close this or keep it open. We have no plan to add those proactively in the near future.