CODEWITHSUNDEEP
sqljsonclickhouse
yarikTri
yarikTri

Reputation: 1

ClickHouse: How to select part of json by paths?

Given an arbitrary JSON in ClickHouse:

{
  "id": 123,
  "name": "John",
  "metadata": {
    "foo": "bar",
    "nums": [1, 2, 3]
  }
}

And an arbitrary list of paths: ['id', 'metadata.nums', 'not.existing'] (or any similar format)

Is there any way to select json's part based on specified paths?
For paths specified above I expect this result:

{
  "id": 123,
  "metadata": {
    "nums": [1, 2, 3]
  }
}

I have considered approaches with built-in functions (like JSON_VALUE with JsonPath or JSONExtractKeysAndValuesRaw), but each of them returns either values without keys or only value by one path

Upvotes: 0

Views: 344

Answers (1)

Thom O'Connor
Thom O'Connor

Reputation: 346

There are likely multiple ways to parse and access this JSON data using ClickHouse - there are quite a few different functions for handling JSON. Here's one example that a String column to store the JSON Tuple data, and then uses JSONExtractString to parse that string when needed:

// Create the table

CREATE TABLE IF NOT EXISTS json_sample
(
    `id` UInt32,
    `name` String,
    `metadata` String
)
ENGINE = MergeTree
ORDER BY id;

// Insert the data from your raw JSON data

INSERT INTO json_sample FROM file('./myJSON.json', 'JSONEachRow');

// Select the data using JSONExtractString

SELECT
    id,
    name,
    JSONExtractString(metadata, 'foo'),
    JSONExtractString(metadata, 'nums')
FROM json_sample;

Query id: ba698471-e5d4-4bcb-8755-7a379145a5f4

┌──id─┬─name─┬─JSONExtractString(metadata, 'foo')─┬─JSONExtractString(metadata, 'nums')─┐
│ 123 │ John │ bar                                │ [1,2,3]                             │
└─────┴──────┴────────────────────────────────────┴─────────────────────────────────────┘

Upvotes: 0

Related Questions