jsonextractInt64从字符串中提取到clickhouse

发布于 2025-02-10 00:17:31 字数 554 浏览 6 评论 0原文

我有一个源表：

CREATE TABLE test.source
(
    text_json String
)
    engine = Memory;

我有一个目标表：

CREATE TABLE test.destination
(
    column Nullable(Int64)
)
    engine = Memory;

我插入到源： 插入test.source（text_json）格式JSONASSTRING {“ column”：null};

然后，我尝试将JSON int值解析并插入目标中

INSERT INTO test.destination (column)
SELECT JSONExtractInt(text_json) FROM test.source;

，但是它将插入0。 - 确定性的行为好像我事先具有真正的零一样，将不可能区分null和0

。 > null ？

原文

I have a source table:

CREATE TABLE test.source
(
    text_json String
)
    engine = Memory;

I have a destination table:

CREATE TABLE test.destination
(
    column Nullable(Int64)
)
    engine = Memory;

I insert to the source:
INSERT INTO test.source (text_json) FORMAT JSONAsString {"column": null};

Then I try to parse json int value and insert into destination

INSERT INTO test.destination (column)
SELECT JSONExtractInt(text_json) FROM test.source;

However, it will insert 0. And it will be a non-deterministic behavior as if I have a real zero beforehand, it will be impossible to differentiate between NULL and 0.

How to parse from JSONString null value into Int64 column AS Null?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

短叹 2025-02-17 00:17:31

您可以使用 json_value 提取字符串值并将其传递给插入（带有隐式或显式铸造）：

INSERT INTO test.destination (column)
SELECT JSON_VALUE(text_json, '$.column')
FROM test.source

结果将为null：

SELECT * FROM test.destination
┌─column─┐
│   ᴺᵁᴸᴸ │
└────────

You can use JSON_VALUE to extract the string value and pass that to INSERT instead (with implicit or explicit casting):

INSERT INTO test.destination (column)
SELECT JSON_VALUE(text_json, '$.column')
FROM test.source

The result will be NULL:

SELECT * FROM test.destination
┌─column─┐
│   ᴺᵁᴸᴸ │
└────────

回复收藏 0 原文

|煩躁 2025-02-17 00:17:31

关于JSON_VALUE，似乎在以后的版本中更改了。我在23.8.16进行了测试，但没有返回nulls：

select 
  JSON_VALUE(text_json, '$.column') "column"
FROM format(
    JSONAsString,
    'text_json String',
    $
    {"column":"not_null"}
    {"column":null}
    {"other_column":"some_value"}
    $);

┌─column───┐
│ not_null │
│ null     │ <-- this is string "null" here, not NULL
│          │ <-- this is empty string here, not NULL
└──────────┘

对我有用的是jsonextract：

select
  JSONExtract(text_json, 'column', 'Nullable(String)') "column"
FROM format(
    JSONAsString,
    'text_json String',
    $
    {"column":"not_null"}
    {"column":null}
    {"other_column":"some_value"}
    $);

┌─column───┐
│ not_null │
│ ᴺᵁᴸᴸ     │ <-- now these are "NULL"s
│ ᴺᵁᴸᴸ     │
└──────────┘

Regarding the JSON_VALUE, seems like it was changed in later versions. I tested on 23.8.16 and it didn't return NULLs:

select 
  JSON_VALUE(text_json, '$.column') "column"
FROM format(
    JSONAsString,
    'text_json String',
    $
    {"column":"not_null"}
    {"column":null}
    {"other_column":"some_value"}
    $);

┌─column───┐
│ not_null │
│ null     │ <-- this is string "null" here, not NULL
│          │ <-- this is empty string here, not NULL
└──────────┘

What worked for me was JSONExtract:

select
  JSONExtract(text_json, 'column', 'Nullable(String)') "column"
FROM format(
    JSONAsString,
    'text_json String',
    $
    {"column":"not_null"}
    {"column":null}
    {"other_column":"some_value"}
    $);

┌─column───┐
│ not_null │
│ ᴺᵁᴸᴸ     │ <-- now these are "NULL"s
│ ᴺᵁᴸᴸ     │
└──────────┘

回复收藏 0 原文

~没有更多了~