Could you change JSON result format as common

[helloworlde]

Hi, I'm developing a IDEA plugin for SOAR, there is a trouble for me when I got JSON result

Feature Description

Current JSON result like this :

{
  "AC4262B5AF150CB5": {
    "CLA.001": {
      "Item": "CLA.001",
      "Severity": "L4",
      "Summary": "最外层SELECT未指定WHERE条件",
      "Content": "SELECT语句没有WHERE子句，可能检查比预期更多的行(全表扫描)。对于SELECT COUNT(*)类型的请求如果不要求精度，建议使用SHOW TABLE STATUS或EXPLAIN替代。",
      "Case": "select id from tbl",
      "Position": 0
    },
    "COL.001": {
      "Item": "COL.001",
      "Severity": "L1",
      "Summary": "不建议使用SELECT * 类型查询",
      "Content": "当表结构变更时，使用*通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。",
      "Case": "select * from tbl where id=1",
      "Position": 0
    },
    "EXP.000": {
      "Item": "EXP.000",
      "Severity": "L0",
      "Summary": "Explain信息",
      "Content": "| id | select\\_type | table | partitions | type | possible_keys | key | key\\_len | ref | rows | filtered | scalability | Extra |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| 1  | SIMPLE | *user* | NULL | ALL |  | NULL |  |  | ☠️ **13670968** | 0.00% | ☠️ **O(n)** | NULL |\n\n",
      "Case": "### Explain信息解读\n\n#### SelectType信息解读\n\n* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).\n\n#### Type信息解读\n\n* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.\n",
      "Position": 0
    }
  }
}

It's not a common format and it's not easy convert as an object directly. Could you please change result as common like this :

{
  "suggestion": [
    {
      "item": "CLA.001",
      "severity": "L4",
      "summary": "最外层SELECT未指定WHERE条件",
      "content": "SELECT语句没有WHERE子句，可能检查比预期更多的行(全表扫描)。对于SELECT COUNT()类型的请求如果不要求精度，建议使用SHOW TABLE STATUS或EXPLAIN替代",
      "case": "select id from tbl",
      "position": 0
    },
    {
      "item": "COL.001",
      "severity": "L1",
      "summary": "不建议使用SELECT  类型查询",
      "content": "当表结构变更时，使用通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据",
      "case": "select  from tbl where id=1",
      "position": 0
    }
  ],
  "explain": {
    "item": "EXP.000",
    "severity": "L0",
    "summary": "Explain信息",
    "explainContent": [
      {
        "id": "1",
        "selectType": "SIMPLE",
        "table": "user",
        "partitions": null,
        "type": "ALL",
        "possible_keys": "",
        "key": " NULL",
        "keyLen": "",
        "ref": "",
        "rows": " ☠️ 13670968",
        "filtered": "0.00%",
        "scalability": "☠️ O(n)",
        "extra": null
      },
      {
        "id": "2",
        "selectType": "SIMPLE",
        "table": "user",
        "partitions": null,
        "type": "ALL",
        "possible_keys": "",
        "key": " NULL",
        "keyLen": "",
        "ref": "",
        "rows": " ☠️ 13670968",
        "filtered": "0.00%",
        "scalability": "☠️ O(n)",
        "extra": null
      }
    ],
    "case": {
      "title": "Explain信息解读",
      "explainInfo": [
        {
          "summary": "SelectType信息解读",
          "content": "简单SELECT(不使用UNION或子查询等)"
        },
        {
          "summary": "Type信息解读",
          "content": "最坏的情况, 从头到尾全表扫描"
        }
      ]
    },
    "position": 0
  },
  "formattedSQL": "SELECT\n  \nFROM \n  user",
  "score": "★ ★ ★ ☆ ☆ 75分",
  "queryId": "AC4262B5AF150CB5"
}

• Add formatted SQL in the result
• Change SQL keywords to upper case
• Change explain as different object
• Add score and query id in result
• Change first letter to lower case and key to camelCase
• Remove last period like "。"

For this, It's convenient for parse JSON for us to develop plugins though now some content is not easy to parse and show. Thank you very much.

[martianzhang]

There is a report-type called lint.

soar -report-type lint -query test.sql