免费注册
帮助文档(华北一、二)

  • 创建json类型的表格,插入数据。

     

    CREATE TABLE test_json(id INT , name json)

    WITH (APPENDONLY=TRUE,ORIENTATION=COLUMN,compresslevel=5) DISTRIBUTED BY (id);

    INSERT INTO test_json VALUES

    (1,'{ "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}}'),

    (2,'{ "id": 20, "sub": { "subid": 200,"subsub": {"subsubid": 2000}}}'),

    (1,'{ "id": 1, "sub": { "subid": "test","subsub": {"subsubid": 100}}}'),

    (3,'{ "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" }');

    json操作类型操作举例:

     

    SELECT * FROM test_json WHERE name->>'id'=1;

     id |                               name

    ----+-------------------------------------------------------------------

      1 | { "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}}

      1 | { "id": 1, "sub": { "subid": "test","subsub": {"subsubid": 100}}}

      3 | { "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" }

    SELECT * FROM test_json WHERE name->'sub'->>'subid'=10;

     id |                             name

    ----+---------------------------------------------------------------

      1 | { "id": 1, "sub": { "subid": 10,"subsub": {"subsubid": 100}}}

    SELECT * FROM test_json WHERE name->>'name'='me';

     id |                          name

    ----+---------------------------------------------------------

      3 | { "id": 1,"sub":"test","name":"me","ip":"10.10.10.10" }



文档是否已解决您的问题?

  已解决   未解决

如您有其它疑问,您也可以与我们技术专家联系探讨。

联系技术专家