How can I fill in one field when inserting data into a ClickHouse table, the value of which I do not pass to json?

I have the json data that the partners send. Json can contain many elements. These items are written to the table, but then I will need to pull the data from the table by specifying the partner's number. My task is to specify the partner ID partnerId for each inserted element. But this value does not exist and should not be in the Json file I received. How to assign partnerId to all elements when importing Json, for example partnerId=100.

CREATE TABLE IF NOT EXISTS customers (
uuid UUID DEFAULT generateUUIDv4(),
partnerID Int32,
userID String,
externalID String,
fullName String,
firstName String,
lastName String,
birthday Date,
gender UInt8,
phoneNumber Int64,
email String,
isEmailVerified UInt8,
timestamp DateTime DEFAULT now(),
firstInteractionDate Date,
createdAt DateTime,
updatedAt DateTime,
defaultAddress String
) ENGINE = AggregatingMergeTree()
PARTITION BY partnerID
ORDER BY (phoneNumber, email, externalID, userID);
SET input_format_json_read_objects_as_strings = 1;

INSERT INTO customers FORMAT JSON [{"userID":"3252345664645454","externalID":"160768","fullName":"Крейг Газовский","firstName":"Крейг","lastName":"Газовский","birthday":"2001-12-20","gender":"1","phoneNumber":"79407496606","email":"79483376098@mail.net","isEmailVerified":true,"createdAt":"2020-01-01","firstInteractionDate":"2020-01-01","updateAt":"2024-02-15","defaultAddress":{"city":"Воронеж","address":"Ленина, дом 5, квартира 10"}},{"userID":"1265875664667599","externalID":"600090","fullName":"Лена Иванова","firstName":"Лена","lastName":"Иванова","birthday":"1975-10-13","gender":"2","phoneNumber":"79415436832","email":"79415436832@mail.net","isEmailVerified":true,"createdAt":"2023-12-24","firstInteractionDate":"2023-12-24","updateAt":"2024-02-15","defaultAddress":{"city":"Москва","address":"Разина, дом 75, квартира 178"}}];

SELECT *
FROM customers 
//WHERE JSONExtractString(defaultAddress, 'city') = 'Москва'
FORMAT Vertical;

or https://fiddle.clickhouse.com/e3886e9f-3bae-4143-b224-df138d327baf

I tried to reproduce the problem in the query constructor, but nothing useful happened.

Solution

You may pass your jsons as array of strings and use an INSERT statement like below.

INSERT INTO customers
(
  partnerID
, userID
, birthday
, gender
, updatedAt
, defaultAddress
-- other columns
)
WITH  
arrayJoin
(
[
  '{"userID":"3252345664645454","externalID":"160768","fullName":"Крейг Газовский","firstName":"Крейг","lastName":"Газовский","birthday":"2001-12-20","gender":"1","phoneNumber":"79407496606","email":"79483376098@mail.net","isEmailVerified":true,"createdAt":"2020-01-01","firstInteractionDate":"2020-01-01","updateAt":"2024-02-15","defaultAddress":{"city":"Воронеж","address":"Ленина, дом 5, квартира 10"}}'
, '{"userID":"1265875664667599","externalID":"600090","fullName":"Лена Иванова","firstName":"Лена","lastName":"Иванова","birthday":"1975-10-13","gender":"2","phoneNumber":"79415436832","email":"79415436832@mail.net","isEmailVerified":true,"createdAt":"2023-12-24","firstInteractionDate":"2023-12-24","updateAt":"2024-02-15","defaultAddress":{"city":"Москва","address":"Разина, дом 75, квартира 178"}}'
]
) AS j
SELECT 
  100                                                   AS partnerID
, visitParamExtractString(j, 'userID')                  AS userID
, toDate(visitParamExtractString(j, 'birthday'))        AS birthday
, visitParamExtractInt(j, 'gender')                     AS gender
, toDateTime(visitParamExtractString(j, 'updateAt'))    AS updatedAt
, JSONExtractRaw(j, 'defaultAddress')                   AS defaultAddress
-- expressions for other columns
;

fiddle