tencent cloud


Querying JSON Data

Last updated: 2024-07-17 16:18:53

    Query steps

    1. Create a data table and specify the JSON format for parsing.
    CREATE EXTERNAL TABLE `order_demo`(
    `docid` string COMMENT 'from deserializer',
    `user` struct < id :int,
    username :string,
    name :string,
    shippingaddress :struct < address1 :string,
    address2 :string,
    city :string,
    state :string > > COMMENT 'from deserializer',
    `children` array < string >
    ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 'cosn://dlc-bucket/order'
    2. Run a query statement to query the JSON data. Data Lake Compute supports json_parse(), json_extract_scalar(), and json_extract() parsing functions.
    SELECT `user`.`shippingaddress`.`address1` FROM `order_demo` limit 10;

    System restraints

    The data must be in complete JSON format; otherwise, Data Lake Compute cannot parse it.
    A data row cannot contain a line break, and the JSON format cannot be optimized visually; for example:
    {"name":"Andy", "age":30}
    {"name":"Justin", "age":19}
    Data Lake Compute will automatically recognize the first JSON level as the attribute column of a data table and recognize other nested structures as corresponding attribute values.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support