jq 指令筆記 - 使用 to_entries 保留 key/value 資料，再用 select / index / match 過濾資料

人生就是有那種怪怪的堅持，明明寫個 php 或 python 就立刻可以解掉的需求，偏偏愛用 jq 來處理 XD

故事是來自於有個 key-value pair 的 json 資料：

% echo '{"A":{"field":"v1"},"B":{"field":"v2"}}' | jq ''
{
  "A": {
    "field": "v1"
  },
  "B": {
    "field": "v2"
  }
}

想要透過 jq 過濾時，也能保留 key 資料，這時就用 to_entries 來達成：

% echo '{"A":{"field":"v1"},"B":{"field":"v2"}}' | jq 'to_entries[]'
[
  {
    "key": "A",
    "value": {
      "field": "v1"
    }
  },
  {
    "key": "B",
    "value": {
      "field": "v2"
    }
  }
]

% echo '{"A":{"field":"v1"},"B":{"field":"v2"}}' | jq 'to_entries[]'
{
  "key": "A",
  "value": {
    "field": "v1"
  }
}
{
  "key": "B",
  "value": {
    "field": "v2"
  }
}

接著要再過濾指定欄位帶有 關鍵字 時，就靠 select 跟 index 來達成：

% echo '{"A":{"field":"v1"},"B":{"field":"v2"}}' | jq 'to_entries[] | select( .value.field | index("v2") >= 0 )'
{
  "key": "B",
  "value": {
    "field": "v2"
  }
}

此例是輸出 value.field 數值帶有 v2 關鍵字。而 index 之外的還有 match 等支援 regular expression 的用法，只是要用 !match 時有點卡卡串不太起來，就乾脆用 index 來處理。

jq 指令筆記 - 整理 JSON 資料，使用 select / index 過濾關鍵字

用 jq 去整理 api/json 的資料的。整個需求是：

• API 回傳的 JSON 資料中，是一個 array 形式，裡頭的元素是 key-value pair
• 透過 jq 把符合我需要的 資料列出
• 檢查在某些條件上，有哪些東西，最後回歸到 comm 的工具幫忙導出結果進行比較

筆記一下 jq 項目：

$ cat /tmp/api.json | jq '.["data"]'
[
  {
    "field": "hello"
  },
  {
    "field": "world"
  }
]
$ cat /tmp/api.json | jq '.["data"] | .[] '
{
  "field": "hello"
}
{
  "field": "world"
}
$ cat /tmp/api.json | jq '.["data"] | .[] | select (.field | index("e") > 0) '
{
  "field": "hello"
}
$ cat /tmp/api.json | jq '.["data"] | .[] | select (.field | index("e") > 0) | .field '
"hello"

如此，可以結果導入檔案，如果需要比較檔案內的差異，就可以用 comm 指令來做事

[SQL] select n rows from each group @ MySQL 5.6

假設有一張 table 名為 log 長這樣:

{
id INTEGER,
level VARCHAR(16),
user VARCHAR(32)
}

mysql> SELECT * FROM log
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
4, "SA", "admin4"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
8, "RD", "programmer4"
9, "RD", "programmer1"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"

有沒有一招可以撈出，讓每個 Level 只顯示 3 筆資料？假想成果：

mysql> SELECT ... FROM log GROUP BY level
1, "SA", "admin1"
2, "SA", "admin2"
3, "SA", "admin3"
5, "RD", "programmer1"
6, "RD", "programmer2"
7, "RD", "programmer3"
10, "FAE", "programmerA"
11, "FAE", "programmerB"
12, "FAE", "programmerC"

土法煉鋼法，用 UNION ALL 來處理：

mysql> SELECT * FROM (SELECT * FROM log WHERE level = 'SA' LIMIT 3) AS t UNION ALL (SELECT * FROM log WHERE level = 'RD' LIMIT 3) UNION ALL (SELECT * FROM log WHERE level = 'FAE' LIMIT 3);

所幸，問了一下強者我同學，得到個關鍵字：GROUP_CONCAT , http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2,admin3"
"RD", "programmer1, programmer2, programmer3"
"FAE", "programmerA, programmerB, programmerC"

如果想限制撈出的資料個數，要設定 group_concat_max_len：

mysql> SET group_concat_max_len = 2;
mysql> SELECT level, GROUP_CONCAT(user) FROM log GROUP BY level;
"SA", "admin1,admin2"
"RD", "programmer1, programmer2"
"FAE", "programmerA, programmerB"

雖然上述結果還不太適合再做 JOIN 來處理，但，已經算佛心了... XD

其他 Google 用的關鍵字："select top n rows from each group"，會看到一些 RANK() OVER(PARTITION BY level) ，但對 MySQL 應該不適用 XD 強者我同學說，若在 PostgreSQL 可以用：

postgrel> select level, array_aggr(user)[0:1] from table group by level;

看來該多給 PostgrelSQL 機會 XD (當初案子用到 GIS 相關 plugin 才有用它...)

此外，跟強者我同學閒聊時，發現去年的一些經驗還滿適合使用的，有些查詢很久的指令，可以考慮定期產生並儲存在另一張 tabel，降低一般 client 觸發複雜的 SQL Query，像是 JOIN, GROUP 等，這也是在大型服務中也常用到的方式。算是此次閒聊最大的心得，因為去年也有應用這個架構來處理服務，驗證自已的(偷懶)做法無誤 XDDD

[SQL] 從 Table 中取出 Column 1 取資料並塞進 Column 2 方法 @ MySQL 5.6

有一張 table 儲存 field1, field2 為原始資料，由於資料尚未驗證，想要透過 REGEXP 驗證資料結果，這時就想到每次找都要驗證一次有點煩，所以就乾脆建立 field3，將驗證過的資料儲存起來。

至於解法方面，就單純把撈出來的資料再 INSERT 回去 :P 假設有一張 Table 名為 mTable，其中 key 為 field1, field2，而 field3 預設可以為空，目標則是將 field1 驗證過的資料儲存在 field3，未來使用時就可以透過 field3 來當作資料驗證。

新增資料用法：

INSERT INTO (field1, field2) VALUES ... ;

驗證 field1 資料，把正確格式擺進 field3：

INSERT mTable (field1, field2, field3)

SELECT field1, field2, field1 AS field3 FROM mTable WHERE field3 IS NULL AND field1 REGEXP '^[0-9A-Za-z]+$'

ON DUPLICATE KEY UPDATE

field3 = VALUES(field3)

如此一來，就完成驗證 :P

接著應該也可以嘗試 MySQL trigger 用法？不過，驗證資料這件事應該不要叫 DB 做才對 XD 只是...環境所限啊。

[SQL] SELECT IN SELECT 以及 Pagination 的使用 @ MySQL 5.6

使用 SQL 語法時，有時會需要從另一張 Table 取出清單，接著對清單內的資料做為基準再進行一次資料的擷取，直觀的想法大概是 SELECT something FROM Table1 WHERE id IN (SELECT id FROM Table2 WHERE ... )。

可惜上述語法是不行的 XD 要改成 JOIN 的做法：

SELECT something FROM Table1, (SELECT id FROM Table2) AS list WHERE Table1.id = list.id;

接著，偶爾會需要 pagination 的需求，加個 LIMIT 的用法，這時候又會想要回報全部有幾筆資料（對於一些搜尋引擎的設計，有些是採用預估的方式），以便前端可以估算有幾筆資料。

最簡單的解法是再用一個 SQL Query 去問 Table2 的 id 資料，但想要更快一點，就來試試 MySQL User-Defined Variables 吧!

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id;

如此一來，結果都會有個 total 筆數跟著，雖然仍不夠好，但也不錯啦 XD  而搭配 LIMIT OFFSET,COUNT 時，total 的資訊是來自掃 Table2 的資料，所以也能正常顯示：

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id LIMIT 0,10;

[SQL] 依據條件取出指定欄位 SELECT IF/ELSE, CASE WHEN/ELSE 用法 @ MySQL 5.6

假設有一張 Table 有兩個欄位：

mysql> SELECT * FROM mtable;
+----+----+
| f1 | f2 |
+----+----+
| 1  | 2  |
| 4  | 3  |
| 5  | 6  |
+----+----+

想要撈出 f1 跟 f2 之中，數值最大者:

mysql> SELECT f1 AS result FROM mtable WHERE f1 > f2;
+--------+
| result |
+--------+
| 4      |
+--------+

mysql> SELECT f2 AS result FROM mtable WHERE f1 < f2;
+--------+
| result |
+--------+
| 2      |
| 6      |
+--------+

這時候，可透過條件判斷，透過 CASE WHEN/ELSE 的用法，就可以不用分兩次撈了：

mysql> SELECT
CASE WHEN f1 > f2
THEN f1
ELSE f2
END AS result
FROM mtable;
+--------+
| result |
+--------+
| 2      |
| 4      |
| 6      |
+--------+