All Products
Search
Document Center

Simple Log Service:Transform complex JSON data

Last Updated:Oct 30, 2023

This topic describes how to use the data transformation feature of Simple Log Service to transform complex JSON data.

Transform complex JSON data with multiple subkeys each of which is an array

Program-built logs are written in a statistical JSON format, usually containing basic information and multiple subkeys each of which is an array. For example, a server writes a log at an interval of 1 minute. The log contains the data information status and the statistical status of servers and clients generating logs.

  • Sample log

    __source__:  192.0.2.1
    __topic__:  
    content:{
         "service": "search_service",
         "overal_status": "yellow",
         "servers": [
             {
                 "host": "192.0.2.1",
                 "status": "green"
             },
             {
                 "host": "192.0.2.2",
                 "status": "green"
             }
         ],
         "clients": [
             {
                 "host": "192.0.2.3",
                 "status": "green"
             },
             {
                 "host": "192.0.2.4",
                 "status": "red"
             }
         ]
    }
  • Data transformation requirements

    1. Split the raw log by topic, including overall_type, client_status, and server_status.

    2. Store different information in each topic as follows:

      • overall_type: stores the server count, client count, overall status (color), and service information.

      • client_status: stores the host IP address, status, and service information.

      • server_status: stores the host IP address, status, and service information.

  • Expected result

    __source__:  192.0.2.1
    __topic__:  overall_type
    client_count:  2
    overal_status:  yellow
    server_count:  2
    service:  search_service
    
    
    __source__:  192.0.2.1
    __topic__:  client_status
    host:  192.0.2.4
    status:  red
    service:  search_service
    
    
    __source__:  192.0.2.1
    __topic__:  client_status
    host:  192.0.2.3
    status:  green
    service:  search_service
    
    
    __source__:  192.0.2.1
    __topic__:  server_status
    host:  192.0.2.1
    status:  green
    service:  search_service
    
    
    __source__:  192.0.2.1
    __topic__:  server_status
    host:  192.0.2.2
    status:  green
    service:  search_service
  • Solution

    1. Split the raw log into three logs and then further split the logs by topic. After the splitting, the three logs have the same information except for the topic field.

      e_set("__topic__", "server_status,client_status,overall_type")
      e_split("__topic__")

      The log after processing is as follows:

      __source__:  192.0.2.1
      __topic__:  server_status         // The topics in the other two logs are client_status and overall_type. Except for the topic field, all the other information in the three logs is the same.
      content:  {
          ... Same as that in the raw log...
      }
    2. Spread the JSON data in the content field at the first layer and delete the content field.

      e_json('content',depth=1)
      e_drop_fields("content")

      The log after processing is as follows:

      __source__:  192.0.2.1
      __topic__:  overall_type              // The topics in the other two logs are client_status and server_status. Except for the topic field, all the other information in the three logs is the same.
      clients:  [{"host": "192.0.2.3", "status": "green"}, {"host": "192.0.2.4", "status": "red"}]
      overal_status:  yellow
      servers:  [{"host": "192.0.2.1", "status": "green"}, {"host": "192.0.2.2", "status": "green"}]
      service:  search_service
    3. For the log with the topic overall_type, compute the values for client_count and server_count.

      e_if(e_search("__topic__==overall_type"), 
           e_compose(
              e_set("client_count", json_select(v("clients"), "length([*])", default=0)), 
              e_set("server_count", json_select(v("servers"), "length([*])", default=0))
        ))

      The log after processing is as follows:

      __topic__:  overall_type
      server_count:  2
      client_count:  2
    4. Delete the clients and servers fields.

      e_if(e_search("__topic__==overall_type"), e_drop_fields("clients", "servers"))
    5. Further split the log with the topic server_status.

      e_if(e_search("__topic__==server_status"), 
           e_compose(
              e_split("servers"), 
              e_json("servers", depth=1)
        ))

      The log is split into the following two logs:

      __topic__:  server_status
      servers:  {"host": "192.0.2.1", "status": "green"}
      host: 192.0.2.1
      status: green
      __topic__:  server_status
      servers:  {"host": "192.0.2.2", "status": "green"}
      host: 192.0.2.2
      status: green
    6. Delete the servers field.

      e_if(e_search("__topic__==overall_type"), e_drop_fields("servers"))
    7. Further split the log with the topic client_status and delete the clients field.

      e_if(e_search("__topic__==client_status"), 
           e_compose(
              e_split("clients"), 
              e_json("clients", depth=1),
              e_drop_fields("clients")
        ))

      The log is split into the following two logs:

      __topic__:  client_status
      host: 192.0.2.3
      status: green
      __topic__:  clients
      host: 192.0.2.4
      status: red
    8. To sum up, use the following LOG domain specific language (DSL) rules:

      # Split the raw log by topic.
      e_set("__topic__", "server_status,client_status,overall_type")
      e_split("__topic__")
      e_json('content',depth=1)
      e_drop_fields("content")
      
      # Process the log with the topic overall_type.
      e_if(e_search("__topic__==overall_type"), 
           e_compose(
              e_set("client_count", json_select(v("clients"), "length([*])", default=0)),
      				e_set("server_count", json_select(v("servers"), "length([*])", default=0))
      ))
      
      # Process the log with the topic server_status.
      e_if(e_search("__topic__==server_status"), 
           e_compose(
              e_split("servers"), 
              e_json("servers", depth=1)
        ))
      e_if(e_search("__topic__==overall_type"), e_drop_fields("servers"))
      
      
      # Process the log with the topic client_status.
      e_if(e_search("__topic__==client_status"), 
           e_compose(
              e_split("clients"), 
              e_json("clients", depth=1),
              e_drop_fields("clients")
        ))

Solution optimization

The preceding solution does not work well if the content.clients or content.servers field is empty. Assume that the raw log is as follows:

__source__:  192.0.2.1
__topic__:  
content:{
            "service": "search_service",
            "overal_status": "yellow",
            "servers": [ ],
            "clients": [ ]
}

If you split this raw log into three logs by using the preceding solution, the logs with the topics client_status and server_status are empty.

__source__:  192.0.2.1
__topic__:  overall_type
client_count:  0
overal_status:  yellow
server_count:  0
service:  search_service


__source__:  192.0.2.1
__topic__:  client_status
service:  search_service
__source__:  192.0.2.1


__topic__:  server_status
host:  192.0.2.1
status:  green
service:  search_service
  • Optimized solution 1

    Check whether the logs with the topics server_status and client_status are empty after the raw log is split. If so, discard the logs.

    # Check whether the log with the topic server_status is empty. If so, discard it. If not, retain it.
    e_keep(op_and(e_search("__topic__==server_status"), json_select(v("servers"), "length([*])")))
    
    # Check whether the log with the topic client_status is empty. If so, discard it. If not, retain it.
    e_keep(op_and(e_search("__topic__==client_status"), json_select(v("clients"), "length([*])")))

    To sum up, use the following LOG DSL rules:

    # Split the raw log by topic.
    e_set("__topic__", "server_status,client_status,overall_type")
    e_split("__topic__")
    e_json('content',depth=1)
    e_drop_fields("content")
    
    # Process the log with the topic overall_type.
    e_if(e_search("__topic__==overall_type"), 
         e_compose(
            e_set("client_count", json_select(v("clients"), "length([*])", default=0)),
    				e_set("server_count", json_select(v("servers"), "length([*])", default=0))
    ))
    
    # (New) Check whether the log with the topic server_status is empty. If so, discard it. If not, retain it. 
    e_keep(op_and(e_search("__topic__==server_status"), json_select(v("servers"), "length([*])")))
    
    # Process the log with the topic server_status.
    e_if(e_search("__topic__==server_status"), 
         e_compose(
            e_split("servers"), 
            e_json("servers", depth=1)
      ))
    e_if(e_search("__topic__==overall_type"), e_drop_fields("servers"))
    
    
    # (New) Check whether the log with the topic client_status is empty. If so, discard it. If not, retain it. 
    e_keep(op_and(e_search("__topic__==client_status"), json_select(v("clients"), "length([*])")))
    
    # Process the log with the topic client_status.
    e_if(e_search("__topic__==client_status"), 
         e_compose(
            e_split("clients"), 
            e_json("clients", depth=1),
            e_drop_fields("clients")
      ))
  • Optimized solution 2

    Check whether a field is empty before splitting the raw log. If the field is not empty, split the raw log based on the field.

    # Set the initial topic.
    e_set("__topic__", "server_status")
    
    # If the content.servers field is not empty, split the raw log to obtain a log with the topic server_status.
    e_if(json_select(v("content"), "length(servers[*])"),
       e_compse(
          e_set("__topic__", "server_status,overall_type"),
          e_split("__topic__")
       ))
    
    # If the content.clients field is not empty, further split the raw log to obtain a log with the topic client_status.
    e_if(op_and(e_search("__topic__==overall_type"), json_select(v("content"), "length(clients[*])")),
       e_compse(
          e_set("__topic__", "client_status,overall_type"),
          e_split("__topic__")
       ))

    To sum up, use the following LOG DSL rules:

    # Split the raw log.
    e_set("__topic__", "server_status")
    
    # If the content.servers field is not empty, split the raw log to obtain a log with the topic server_status.
    e_if(json_select(v("content"), "length(servers[*])"),
       e_compse(
          e_set("__topic__", "server_status,overall_type"),
          e_split("__topic__")
       ))
    
    # If the content.clients field is not empty, further split the raw log to obtain a log with the topic client_status.
    e_if(op_and(e_search("__topic__==overall_type"), json_select(v("content"), "length(clients[*])")),
       e_compse(
          e_set("__topic__", "client_status,overall_type"),
          e_split("__topic__")
       ))
    
    # Process the log with the topic overall_type.
    e_if(e_search("__topic__==overall_type"), 
         e_compose(
            e_set("client_count", json_select(v("clients"), "length([*])", default=0)),
    				e_set("server_count", json_select(v("servers"), "length([*])", default=0))
    ))
    
    # Process the log with the topic server_status.
    e_if(e_search("__topic__==server_status"), 
         e_compose(
            e_split("servers"), 
            e_json("servers", depth=1)
      ))
    e_if(e_search("__topic__==overall_type"), e_drop_fields("servers"))
    
    
    # Process the log with the topic client_status.
    e_if(e_search("__topic__==client_status"), 
         e_compose(
            e_split("clients"), 
            e_json("clients", depth=1),
            e_drop_fields("clients")
      ))

Solution comparison

  • Solution 1 is redundant in logic because it deletes empty logs after obtaining them from the raw log. However, the rules are simple and easy to maintain. We recommend that you use this solution by default.

  • Solution 2 has higher processing efficiency because it checks for empty fields before splitting. However, this solution uses redundant rules. We recommend that you use this solution only for specific scenarios, for example, when a large number of additional events may be produced after the raw log is split.

Transform complex JSON data with multiple layers of nested array objects

Take the following complex JSON data with multiple layers of nested arrays as an example. Assume that you want to split the logon information stored in login_histories of different objects in the users field into separate logon events.

  • Raw log

    __source__:  192.0.2.1
    __topic__:  
    content:{
      "users": [
        {
            "name": "user1",
            "login_histories": [
              {
                "date": "2019-10-10 0:0:0",
                "login_ip": "192.0.2.6"
              },
              {
                "date": "2019-10-10 1:0:0",
                "login_ip": "192.0.2.6"
              },
          {
          ... More logon information...
          }
            ]
        },
        {
            "name": "user2",
            "login_histories": [
              {
                "date": "2019-10-11 0:0:0",
                "login_ip": "192.0.2.7"
              },
              {
                "date": "2019-10-11 1:0:0",
                "login_ip": "192.0.2.9"
              },
          {
          ... More logon information...
          }     
            ]
        },
      {
        ... More users...
      }
      ]
    }
  • Expected logs after splitting

    __source__:  192.0.2.1
    name:  user1
    date:  2019-10-11 1:0:0
    login_ip:  192.0.2.6
    
    __source__: 192.0.2.1
    name:  user1
    date:  2019-10-11 0:0:0
    login_ip:  192.0.2.6
    
    __source__:  192.0.2.1
    name:  user2
    date:  2019-10-11 0:0:0
    login_ip:  192.0.2.7
    
    __source__: 192.0.2.1
    name:  user2
    date:  2019-10-11 1:0:0
    login_ip:  192.0.2.9  
    
    ... More logs...
  • Solution

    1. Split the log and spread data based on users in the content field.

      e_split("content", jmes='users[*]', output='item')
      e_json("item",depth=1)

      The log after processing is as follows:

      __source__:  192.0.2.1
      __topic__:  
      content:{... Same as that in the raw log...}
      item:  {"name": "user1", "login_histories": [{"date": "2019-10-10 0:0:0", "login_ip": "192.0.2.6"}, {"date": "2019-10-10 1:0:0", "login_ip": "192.0.2.6"}]}
      login_histories:  [{"date": "2019-10-10 0:0:0", "login_ip": "192.0.2.6"}, {"date": "2019-10-10 1:0:0", "login_ip": "192.0.2.6"}]
      name:  user1
      
      __source__:  192.0.2.1
      __topic__:  
      content:{... Same as that in the raw log...}
      item:  {"name": "user2", "login_histories": [{"date": "2019-10-11 0:0:0", "login_ip": "192.0.2.7"}, {"date": "2019-10-11 1:0:0", "login_ip": "192.0.2.9"}]}
      login_histories:  [{"date": "2019-10-11 0:0:0", "login_ip": "192.0.2.7"}, {"date": "2019-10-11 1:0:0", "login_ip": "192.0.2.9"}]
      name:  user2
    2. Split the log and spread data based on login_histories.

      e_split("login_histories")
      e_json("login_histories", depth=1)

      The log after processing is as follows:

      __source__:  192.0.2.1
      __topic__: 
      content: {... Same as that in the raw log...}
      date:  2019-10-11 0:0:0
      item:  {"name": "user2", "login_histories": [{"date": "2019-10-11 0:0:0", "login_ip": "192.0.2.7"}, {"date": "2019-10-11 1:0:0", "login_ip": "192.0.2.9"}]}
      login_histories:  {"date": "2019-10-11 0:0:0", "login_ip": "192.0.2.7"}
      login_ip:  192.0.2.7
      name:  user2
      
      __source__:  192.0.2.1
      __topic__: 
      content: {... Same as that in the raw log...}
      date:  2019-10-11 1:0:0
      item:  {"name": "user2", "login_histories": [{"date": "2019-10-11 0:0:0", "login_ip": "192.0.2.7"}, {"date": "2019-10-11 1:0:0", "login_ip": "192.0.2.9"}]}
      login_histories:  {"date": "2019-10-11 1:0:0", "login_ip": "192.0.2.9"}
      login_ip:  192.0.2.9
      name:  user2
      
      __source__: 192.0.2.1
      __topic__:  
      content: {... Same as that in the raw log...}
      date:  2019-10-10 1:0:0
      item:  {"name": "user1", "login_histories": [{"date": "2019-10-10 0:0:0", "login_ip": "192.0.2.6"}, {"date": "2019-10-10 1:0:0", "login_ip": "192.0.2.6"}]}
      login_histories:  {"date": "2019-10-10 1:0:0", "login_ip": "192.0.2.6"}
      login_ip:  192.0.2.6
      name:  user1
      
      __source__: 192.0.2.1
      __topic__:  
      content: {... Same as that in the raw log...}
      date:  2019-10-10 0:0:0
      item:  {"name": "user1", "login_histories": [{"date": "2019-10-10 0:0:0", "login_ip": "192.0.2.6"}, {"date": "2019-10-10 1:0:0", "login_ip": "192.0.2.6"}]}
      login_histories:  {"date": "2019-10-10 0:0:0", "login_ip": "192.0.2.6"}
      login_ip:  192.0.2.6
      name:  user1
    3. Delete irrelevant fields.

      e_drop_fields("content", "item", "login_histories")

      The log after processing is as follows:

      __source__: 192.0.2.1
      __topic__:
      name:  user1
      date:  2019-10-11 1:0:0
      login_ip:  192.0.2.6
      
      __source__:  192.0.2.1
      __topic__:
      name:  user1
      date:  2019-10-11 0:0:0
      login_ip:  192.0.2.6
      
      __source__:  192.0.2.1
      __topic__:
      name:  user2
      date:  2019-10-11 0:0:0
      login_ip:  192.0.2.7
      
      __source__: 192.0.2.1
      __topic__:
      name:  user2
      date:  2019-10-11 1:0:0
      login_ip:  192.0.2.9
    4. To sum up, use the following LOG DSL rules:

      e_split("content", jmes='users[*]', output='item')
      e_json("item",depth=1)
      e_split("login_histories")
      e_json("login_histories", depth=1)
      e_drop_fields("content", "item", "login_histories")

Conclusion: If you have requirements similar to the above, split the log, spread data based on specified fields, and then delete irrelevant fields.