tencent cloud


Querying JSON Data

Last updated: 2022-08-16 09:41:59

    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