Cant Insert Data from Nested Json in Clickhouse

Question

I have some json file, i save in s3, the structure is like this

{
  "testing_field": "tambahan",
  "test_update_2": {
    "code_1": "5B72F80DA8",
    "path_2": "xxx",
    "filename_2": "xxx.pdf",
    "qrcode_2": "xxx"
  }
}

I tried the select function first to test the data retrieval, like this,

SELECT
test_update_2.filename_2
FROM s3(
    'xxxx/sample_update_field.json',
    'xxx',
    'xxx'
)

i can get the result, like this,

xxx.pdf
xxx.pdf

after that, i create a new field in table, because i search and get this notes

You cannot change the column name by adding a dot (.) in the column name directly because ClickHouse uses the dot (.) as a separator between the database and table name. Therefore, using a dot in the column name will cause a conflict with the existing syntax.

I made the column names as follows

Executing query: ALTER TABLE sidebar_staging ADD COLUMN IF NOT EXISTS testing_field Nullable(String);
Executing query: ALTER TABLE sidebar_staging ADD COLUMN IF NOT EXISTS test_update_2_code_1 Nullable(String);
Executing query: ALTER TABLE sidebar_staging ADD COLUMN IF NOT EXISTS test_update_2_filename_2 Nullable(String);
Executing query: ALTER TABLE sidebar_staging ADD COLUMN IF NOT EXISTS test_update_2_path_2 Nullable(String);
Executing query: ALTER TABLE sidebar_staging ADD COLUMN IF NOT EXISTS test_update_2_qrcode_2 Nullable(String);

I replace the (.) to (_)

But when i try to insert with this query

INSERT INTO example_db.sidebar_staging (test_update_2_filename_2)
SELECT
test_update_2.filename_2
FROM s3(
    'xxx/sample_update_field.json',
    'xxx'
    'xxx'
)

data not entered into the destination field,

then i thing i must add alias, and i try this query,

INSERT INTO example_db.sidebar_staging (test_update_2_filename_2)
    SELECT
    test_update_2.filename_2 as test_update_2_filename_2
    FROM s3(
        'xxx/sample_update_field.json',
        'xxx'
        'xxx'
    )

but the result is same, i think i must be test the testing_field because is not in nested json, with this code

INSERT INTO example_db.sidebar_staging (testing_field)
SELECT
testing_field
FROM s3(
    'xxx/sample_update_field.json',
    'xxx',
    'xxx'
)

What is surprising is that when the field name from the JSON is the same as the field name in the table, it does not require an alias and can be inserted into the target table.

Because testing_field is same in column table testing_field

So the question is, how do you do an insert if the original field is in a nested JSON? I have to extract with dot format (test update_2.filename 2) to get the value, in other side i cant use dot in name column and cant used alias to mapping to column too

ViAchKoN · Accepted Answer · 2024-10-03 23:03:28Z

0

Have you tried using JSONExtract method (link to doc)? You should be able to achieve what you want by something like this:

INSERT INTO example_db.sidebar_staging (test_update_2_filename_2)
SELECT
    JSONExtract(test_update_2, 'filename_2', 'String') AS test_update_2_filename_2
FROM s3(
    'xxx/sample_update_field.json',
    'xxx',
    'xxx'
)

answered Oct 3, 2024 at 23:03

ViAchKoN

8272 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cant Insert Data from Nested Json in Clickhouse

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related