sqlite3 の json_object の制限の話

# Overview

ちょっと前に sqlite3 で json 出力するやり方を書いたんですが、
https://qiita.com/arc279/items/bba7f6be362e376f06ab#json-object-%E3%81%A7%E5%87%BA%E5%8A%9B%E3%81%99%E3%82%8B

`json_object` の引数の数が 128を超えると

```
Error: too many arguments on function json_object
```

とか出て失敗するので、回避策を考えてみたけど、`json` でゴリ押しするしかなかった話。

## cf.

https://www.sqlite.org/json1.html

# 最初に結果を貼ります

## 63カラムまではこれでOK

```sql:v1.sql
WITH q1 AS (
/*
  ": X'22'
  ': X'27'
*/
SELECT
  X'27' || name || X'27' as a,
  X'22' || name || X'22' as b
FROM PRAGMA_TABLE_INFO('c63')
)

SELECT
  'SELECT json_object('
    || GROUP_CONCAT(a || ',' || b, ',')
    || ') FROM c63'
FROM q1
```

## 64カラム超の場合はこっちで

```sql:v2.sql
WITH q1 AS (
/*
  ": X'22'
  ': X'27'
*/
SELECT
  X'27' || X'22' || name || X'22' || ':' || X'27' || ' || json_quote(' || X'22' || name || X'22' || ')' as s
FROM PRAGMA_TABLE_INFO('c64')
)

SELECT
  "SELECT"
  || " json('{' || "
  || GROUP_CONCAT(s, " || ',' || ")
  || " || '}' )"
  || " FROM c64"
FROM q1
```

# 以下、細かい話

```bash
$ sqlite3 --version
3.32.3 2020-06-18 14:00:33 7ebdfa80be8e8e73324b8d66b3460222eb74c7e9dfd655b48d6ca7e1933cc8fd
```

## 適当なデータを用意します

```bash
#!/bin/bash

rm -rf a.db

# 10行 * 64カラム
{ seq -f 'c%g' 64; seq -f 'd%g' 640; }  \
| paste -d, $(yes - | head -n 64 | paste -s) \
| sqlite3 -csv a.db ".import /dev/stdin c64"

# 10行 * 63カラム
{ seq -f 'c%g' 63; seq -f 'd%g' 630; }  \
| paste -d, $(yes - | head -n 63 | paste -s) \
| sqlite3 -csv a.db ".import /dev/stdin c63"
```

これに対して

## 出力クエリを生成

こんな感じのクエリを流せば json で出力できるので、

```sql
SELECT json_object('c1',"c1",'c2',"c2",'c3',"c3",'c4',"c4",'c5',"c5",'c6',"c6",'c7',"c7",'c8',"c8",'c9',"c9",'c10',"c10",'c11',"c11",'c12',"c12",'c13',"c13",'c14',"c14",'c15',"c15",'c16',"c16",'c17',"c17",'c18',"c18",'c19',"c19",'c20',"c20",'c21',"c21",'c22',"c22",'c23',"c23",'c24',"c24",'c25',"c25",'c26',"c26",'c27',"c27",'c28',"c28",'c29',"c29",'c30',"c30",'c31',"c31",'c32',"c32",'c33',"c33",'c34',"c34",'c35',"c35",'c36',"c36",'c37',"c37",'c38',"c38",'c39',"c39",'c40',"c40",'c41',"c41",'c42',"c42",'c43',"c43",'c44',"c44",'c45',"c45",'c46',"c46",'c47',"c47",'c48',"c48",'c49',"c49",'c50',"c50",'c51',"c51",'c52',"c52",'c53',"c53",'c54',"c54",'c55',"c55",'c56',"c56",'c57',"c57",'c58',"c58",'c59',"c59",'c60',"c60",'c61',"c61",'c62',"c62",'c63',"c63") FROM c63
```

これを生成する meta クエリが `v1.sql` です。

```bash:jsonで出力
$ sqlite3 a.db <v1.sql | sqlite3 a.db

# 結果は省略
```

## で、何が問題か

`json_object` の引数は `('label', 'value', ...` 形式になっており、
かつ引数の数が128を超えると最初に書いた通りエラーになるので、
63 カラムまでしか出力できない問題にぶち当たるわけです。

```sql:ng.sql
SELECT json_object('c1',"c1",'c2',"c2",'c3',"c3",'c4',"c4",'c5',"c5",'c6',"c6",'c7',"c7",'c8',"c8",'c9',"c9",'c10',"c10",'c11',"c11",'c12',"c12",'c13',"c13",'c14',"c14",'c15',"c15",'c16',"c16",'c17',"c17",'c18',"c18",'c19',"c19",'c20',"c20",'c21',"c21",'c22',"c22",'c23',"c23",'c24',"c24",'c25',"c25",'c26',"c26",'c27',"c27",'c28',"c28",'c29',"c29",'c30',"c30",'c31',"c31",'c32',"c32",'c33',"c33",'c34',"c34",'c35',"c35",'c36',"c36",'c37',"c37",'c38',"c38",'c39',"c39",'c40',"c40",'c41',"c41",'c42',"c42",'c43',"c43",'c44',"c44",'c45',"c45",'c46',"c46",'c47',"c47",'c48',"c48",'c49',"c49",'c50',"c50",'c51',"c51",'c52',"c52",'c53',"c53",'c54',"c54",'c55',"c55",'c56',"c56",'c57',"c57",'c58',"c58",'c59',"c59",'c60',"c60",'c61',"c61",'c62',"c62",'c63',"c63",'c64',"c64") FROM c64
```

```bash:駄目な例
$ sqlite3 a.db <ng.sql
Error: near line 1: too many arguments on function json_object
```

## 代替案

仕方ないので抜け道を考えるワケですけど、
結局自前で json 文字列を整形するしかなさそうなので、

```sql:ok.sql
SELECT json('{' || '"c1":' || json_quote("c1") || ',' || '"c2":' || json_quote("c2") || ',' || '"c3":' || json_quote("c3") || ',' || '"c4":' || json_quote("c4") || ',' || '"c5":' || json_quote("c5") || ',' || '"c6":' || json_quote("c6") || ',' || '"c7":' || json_quote("c7") || ',' || '"c8":' || json_quote("c8") || ',' || '"c9":' || json_quote("c9") || ',' || '"c10":' || json_quote("c10") || ',' || '"c11":' || json_quote("c11") || ',' || '"c12":' || json_quote("c12") || ',' || '"c13":' || json_quote("c13") || ',' || '"c14":' || json_quote("c14") || ',' || '"c15":' || json_quote("c15") || ',' || '"c16":' || json_quote("c16") || ',' || '"c17":' || json_quote("c17") || ',' || '"c18":' || json_quote("c18") || ',' || '"c19":' || json_quote("c19") || ',' || '"c20":' || json_quote("c20") || ',' || '"c21":' || json_quote("c21") || ',' || '"c22":' || json_quote("c22") || ',' || '"c23":' || json_quote("c23") || ',' || '"c24":' || json_quote("c24") || ',' || '"c25":' || json_quote("c25") || ',' || '"c26":' || json_quote("c26") || ',' || '"c27":' || json_quote("c27") || ',' || '"c28":' || json_quote("c28") || ',' || '"c29":' || json_quote("c29") || ',' || '"c30":' || json_quote("c30") || ',' || '"c31":' || json_quote("c31") || ',' || '"c32":' || json_quote("c32") || ',' || '"c33":' || json_quote("c33") || ',' || '"c34":' || json_quote("c34") || ',' || '"c35":' || json_quote("c35") || ',' || '"c36":' || json_quote("c36") || ',' || '"c37":' || json_quote("c37") || ',' || '"c38":' || json_quote("c38") || ',' || '"c39":' || json_quote("c39") || ',' || '"c40":' || json_quote("c40") || ',' || '"c41":' || json_quote("c41") || ',' || '"c42":' || json_quote("c42") || ',' || '"c43":' || json_quote("c43") || ',' || '"c44":' || json_quote("c44") || ',' || '"c45":' || json_quote("c45") || ',' || '"c46":' || json_quote("c46") || ',' || '"c47":' || json_quote("c47") || ',' || '"c48":' || json_quote("c48") || ',' || '"c49":' || json_quote("c49") || ',' || '"c50":' || json_quote("c50") || ',' || '"c51":' || json_quote("c51") || ',' || '"c52":' || json_quote("c52") || ',' || '"c53":' || json_quote("c53") || ',' || '"c54":' || json_quote("c54") || ',' || '"c55":' || json_quote("c55") || ',' || '"c56":' || json_quote("c56") || ',' || '"c57":' || json_quote("c57") || ',' || '"c58":' || json_quote("c58") || ',' || '"c59":' || json_quote("c59") || ',' || '"c60":' || json_quote("c60") || ',' || '"c61":' || json_quote("c61") || ',' || '"c62":' || json_quote("c62") || ',' || '"c63":' || json_quote("c63") || ',' || '"c64":' || json_quote("c64") || '}' ) FROM c64
```

```bash:これならOK
$ sqlite3 a.db <ok.sql

# 結果は省略
```

冒頭の `v2.sql` になるわけですが、
読み解いてもただひたすらに不毛なだけなので、
もし必要なら脳死でコピペしてください（笑）

おわり。