[Umbrella] JSON type improvements

[Avogar]

This issue contains tasks for further improvements of new JSON data type. Continuation of #54864.

Important tasks:

• Reduce memory usage during insertion into JSON column and during merges by better buffer size managing - Reduce memory usage of inserts to JSON by using adaptive write buffer size #69272
• Support ALTER to new JSON type from String/Map/Tuple/ - Support alter from String to JSON #70442
• Support ALTER to new JSON type from Object - Support ALTER from Object to JSON #71784
• Support CAST from Map/Tuple/ Object types to new JSON - Implement simple CAST from Map/Tuple/Object to new JSON through serialization/deserialization from JSON string #71320
• Support CAST between JSON types with different parameters (skip paths, limits, type hints) - Support CAST and ALTER between JSON types with different parameters #72303
• Try to implement default implementation for functions for Dynamic type (so any function will be executed on underlying types dynamically and throw an exception on first incompatible type) - Support Dynamic type in most functions #69691
• Add aggregate functions distinctDynamicTypes/distinctJSONPaths/distinctJSONPathsAndTypes for better introspection of the content in the JSON column - Add aggregate functions distinctDynamicTypes/distinctJSONPaths/distinctJSONPathsAndTypes #68463
• Allow to insert/select JSON from binary strings in RowBinary format (Support JSON deserialization from binary string  #69443) - Allow to read/write JSON type as binary string in RowBinary format #70288
• Allow to insert/select JSON from single String column in Native format (Support JSON serialization to a string in Native format #70281) - Allow to serialize/deserialize JSON column as single String column in Native format #70312
• Support JSON subcolumns in ORDER BY and data-skipping indices expression during table creation - Support subcolumns in MergeTree sorting key and skip indexes #72644
• Support Dynamic type in ifNull/coalesce functions - Support Dynamic type in functions ifNull and coalesce #72772
• Support Dynamic type in functions toFloat64 and similar - Support Dynamic in functions toFloat64/touInt32/etc #72989
• Support equal comparison between JSON values - Support equal comparison for JSON column #72991
• Try to improve subcolumns formatting - Improve formatting of identifiers with JSON subcolumns #73085
• Try to support Nullable(JSON) - Support Nullable(JSON) #73556
• Support subcolumns in MaterializedView queries - Support subcolumns in materialized view select query #74030
• Support referring JSON subcolumns in default and materialized exressions - Support subcolumns in default and materialized expressions #74403.
• Improve performance of reading the whole JSON column from wide parts - Improve performance of the whole JSON column reading in Wide parts from S3 #74827
• Improve performance of subcolumns reading from compact parts - Save marks for each substream in Compact part to be able to read individual subcolumns #77940
• Add new serialization for Dynamic and JSON without SharedVariant/SharedData for better integrations - Implement flattened serialization for Dynamic and JSON in Native format #80499
• Support ALTER UPDATE for JSON/Dynamic types - Allow ALTER UPDATE in JSON and Dynamic columns #82419
• Use Array(Dynamic) instead of unnamed Tuple for arrays of values with different types - Use Array(Dynamic) instead of unnamed tuple for arrays of values with different data types in JSON subcolumn types inference #74937
• Improve deserialization of JSON subcolumns from shared data - Significantly improve performance of JSON subcolumns reading from shared data in MergeTree #83777
• Remove old Object data type - Remove deprecated Object type #85718
• Support JSON in tupleElement function - Support JSON type in tupleElement #91327
• Add setting type_json_skip_invalid_typed_paths to use default value during bad parsing of a path with a hint type - feat(json): add type_json_skip_invalid_typed_paths setting #89886
• Optimize distinctJSONPaths aggregate function so it reads only metadata files with the list of paths - Optimize distinctJSONPaths aggregate function #92196
• Optimize insertion into JSON column - Slightly optimize parsing of JSON type #93614 and Slightly optimize squashing of JSON columns for some cases #94247
• Add syntax json.$a.b for union of json.a.b and json.^a.b or function for such use case - Introduce new "combined" subcolumn for JSON data type that combines literal and sub-object values #98788
• Support JSON type in JSONExtract* functions - Support for native JSON type in JSONExtract* functions. Fixes #88370. #96711
• Support creating indexes on JSONAllPaths function similar to mapKeys for Map type - Add MergeTree skip index support for JSON paths using JSONAllPaths with bloom_filter, tokenbf_v1, ngrambf_v1, and text (inverted) index types #98886

Tasks with no priority:

• Improve alters of JSON/Dynamic columns in Wide part (avoid whole part rewrite)
• Replace JSON column in the subquery to requested subcolumns Replace JSON column in the subquery to requested subcolumns #75538
• Add setting max_subcolumns_to_read.
• Add support for EPHEMERAL subcolumns in JSON paths types declaration.
• Add support for TTL for JSON subcolumns.
• Add support for CODEC for JSON subcolumns.
• Support ALTER UPDATE for individual subcolumns
• Add function and aggregate function to merge JSON objects
• Match json and jsonb type in PostgreSQL to new JSON type under a setting
• Support JSON type in MongoDB engine (Load Mongo documents straight into JSON type #71970)
• Add function JSONAllValues that returns the list of values (casted to String maybe) stored in JSON column in sorted by path order
• Add new JSON type to schema inference
• Consider adding new syntax to read subcolumns by regex (comment)
• Consider implementing function for querying paths dynamically (comment)
• Add information about subcolumn sizes in system table.
• Support writing json.array[N].key for Array(JSON) subcolumns (comment)
• Support declaring the type for all dynamic subcolumns (comment)
• Support Dynamic type as first argument of function has
• Add function like jsonuntuple(json, array_of_paths) to select paths as separate columns
• Support arrayElement function for JSON to read subcolumns like json['key'] and use optimization functions to subcolumns here
• Add new syntax in JSON type definition to define type for path regexp (comment)
• Support JSON in BSONEachRow format
• Add functions JSONRemoveKeys and JSONUpdateKeys
• Support subcolumns in ReplacingMergeTree arguments for version and is_deleted columns
• Consider adding function that converts JSON to Map(String, Dynamic)

Feel free to add any ideas for improvements/feature requests in the comments in this issue

[Avogar]

Right now we have 2 different syntax to read subcolumns: json.a.b and json.^a.b. First will read only path a.b where this path has actual value and is not a nested object and return type Dynamic. Second will read only rows where a.b is a nested object and return type JSON.
For example:

create table test (json JSON) engine=Memory;
inesrt into test values ('{"a" : {"b" : 42}}'), ('"a" : {"b" : {"c" : 42}}');
select json.a.b, json.^a.b from test;

┌─json.a.b─┬─json.^`a`.b─┐
│ 42       │ {}          │
│ ᴺᵁᴸᴸ     │ {"c":"42"}  │
└──────────┴─────────────┘

Does it make sense to add also third syntax like json.$a.b that will return both nested objects and values of this specific path? The type of such subcolumn will be Dynamic where could be any types including JSON:

select json.$a.b from test;

┌──json.&a.b──┐
│ 42          │
│ {"c":"42"}  │
└─────────────┘

Reading of such subcolumn will have the same performance as reading json.^a.b subcolumn (reading json.a.b still the most optimal). WDYT?

[UnamedRus]

There is some confusion between "nested object" and "JSON", i guess

So for example, a single string or number would be valid JSON.

As, just simple value is valid JSON, so technically if ^. syntax meant to be used only with JSON type as result, it should return both 42 and {"c":"42"}

[lukel-x]

Does it make sense to add also third syntax like json.$a.b that will return both nested objects and values of this specific path?

I will definitely use that if implemented @Avogar

[Avogar]

There is some confusion between "nested object" and "JSON", i guess

Yes, unfortunately there is a confusion. Our new JSON type is actually Object type that can store only objects (so name of preivious implementation Object('json') is actually better) , and syntax json.^a.b is reading nested Objects with path a.b, so it returns our type JSON.
Basically we can use Dynamic type to store any valid JSON value (any data type including new JSON) and extract any value from it (using subcolumns) and new syntax like json.$a.b will do exactly it - extract a.b path as Dynamic column with all values from this path including nested objects.

[hkrutzer]

Can we have a JSON column in an AggregatingMergeTree where multiple JSON objects will be merged into one?

[Avogar]

Can we have a JSON column in an AggregatingMergeTree where multiple JSON objects will be merged into one?

We will need to add merge aggregate function for merging JSONs (not sure about the name, maybe mergeJSON/jsonMerge). It's a good idea, will add it to the list of tasks. Also make sense to add usual function to merge several JSON columns into one

[UnamedRus]

Our new JSON type is actually Object type that can store only objects

And it's planned to stay in that way in future?
Just asking about compatibility with standard.

[Avogar]

And it's planned to stay in that way in future?

Right now we plan to keep it this way.

[jfmontanaro]

Hello, I've been experimenting with the new JSON type recently and really like it so far. :)

Is there any possibility of eventually being able to add data-skipping indices on JSON subcolumns? E.g. suppose I have a JSON column event_detail with a source_ip field, I'd like to be able to add a bloom filter index on the source_ip field so that I can easily query on it. Right now attempting to create this index just fails with Missing columns: 'event_detail.source_ip'

I've been able to work around it for now by creating a materialized column that pulls out the value I'm interested in, like so:

ALTER TABLE events ADD COLUMN source_ip IPv6 MATERIALIZED JSONExtract(event_detail::String, 'source_ip', 'IPv6')

And then indexing that column. (It has to be materialized, seemingly, I've tried using an ALIAS column but I can't get it to use the index.)

However, this seems pretty inefficient in both CPU time and storage, so it would be really nice to be able to index JSON subcolumns directly.

[Avogar]

Is there any possibility of eventually being able to add data-skipping indices on JSON subcolumns?

Hi! Right now only using materialized columns as you described. We will work on the support for JSON subcolumns in ORDER BY section in MergeTree tables and then can try to support it for data-skipping indices as well. I will add it to the list of tasks.

[jfmontanaro]

Thank you!

Really liking the JSON stuff so far, btw. Very convenient for allowing event sources to just emit whatever is most convenient, without having to go specify it in advance somewhere in an entirely different system. Thanks for the update!

[nakulogale]

Can we have a way to index the JSON Values?
Refer: #68463 (comment)

TLDR:
I want to create an index on the JSON paths and values in the JSON.
The paths are indexable like so:
alter table json_test add index idx_json JSONAllPaths(json) TYPE bloom_filter(0.01) GRANULARITY 1;
However, there doesnt seem to be a way to index the values natively.
We should have a function as below JSONAllValues(json).