SQLでjson状態でSELECTできませんか？

Question

SQLでjson状態でSELECTできませんか？

イメージは以下です。

SELECT 
  u.id,
  u.old,
  '[{ja:n.ja},{en:n.en}]' AS names
FROM users u
    LEFT JOIN names n ON n.id=u.id
WHERE u.old > 10

目的は以下です。namesを、取得結果を用いたjsonにしたい（「n.ja」を「佐藤」にしたい）のです。

id	old	names
2	20	[{ja:佐藤},{en:Satou}]

テーブルは以下です。

CREATE TABLE users
    (`id` int, `old` int, `name` varchar(6))
;
INSERT INTO users
    (`id`, `old`, `name`)
VALUES
    (1, 10, 1),
    (2, 20, 2)
;

CREATE TABLE names
    (`id` int, `ja` varchar(10), `en` varchar(10))
;
    
INSERT INTO names
    (`id`, `ja`, `en`)
VALUES
    (1, '佐藤', 'Satou'),
    (2, '山田', 'Yamada')
;

こうとか...うーん、PHPでやるしかないでしょうか。

SELECT 
  u.id,
  u.old,
  '[{ja:' . n.ja . '},{en:' . n.en . '}]' AS names
FROM users u
    LEFT JOIN names n ON n.id=u.id
WHERE u.old > 10

Comments

[退会済みユーザー]

DB サーバーは何か (SQL Server? MySQl? Oracle? その他？) ぐらいは書けませんか？

[imuri]

Oracle以外ならどれでもいいですね

[imuri]

最悪Oracleでもいいです、できますか？

[imuri]

MySQLかSQLliteなら尚よしという感じです

[退会済みユーザー]

SQL の書き方には DB によって方言のような違いがあることを認識してますか？ 全部調べて書くなんてできないので、どれか一つあなたが使いそうなものを決められませんか？

[退会済みユーザー]

あと、あなたが質問に書いた取得結果の names は JSON として正しい形式ではないですよ。そこも見直して、上で聞いた DB は何かと合わせて質問欄を編集して書き直してください。

[sazi]

WHERE u.old > 10
という条件が必須なら結果的にinner joinのものしか抽出されないのでleft joinは無意味ですが、left joinにする意図は何でしょう？

Answer 1

SQLServer（2017から？）とOracle（12cから？）にはselect結果をそのままJSONに変換する機能が備わっています。

[sqlserver] クエリの末尾にfor json pathなどをつけるだけ。
https://docs.microsoft.com/ja-jp/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15

[oracle] select句内で関数を使って出力。"目的"のイメージに一番近い出力が可能かもしれない。
https://docs.oracle.com/cd/E82638_01/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先のタイトル](http...) に修正してください。

[hope_mucci]

ご指摘感謝します。修正しました。

Answer 2

mysqlで8.0以上ならJSON_OBJECTAGGが有効かもしれません

Answer 3

データベースで利用できる関数や演算子によりますが、基本的には文字連結で実現できると思います。
ただし、エスケープは考慮していません。

concat関数の場合

SELECT 
  u.id,
  u.old,
  concat('[{ja:',n.ja,'},{en:',n.en,'}]') AS names
FROM users u
    LEFT JOIN names n ON n.id=u.id
WHERE u.old > 10;

連結演算子が||の場合

SELECT 
  u.id,
  u.old,
  '[{ja:'||n.ja||'},{en:'||n.en||'}]' AS names
FROM users u
    LEFT JOIN names n ON n.id=u.id
WHERE u.old > 10;

連結演算子が+の場合

SELECT 
  u.id,
  u.old,
  '[{ja:'+n.ja+'},{en:'+n.en+'}]' AS names
FROM users u
    LEFT JOIN names n ON n.id=u.id
WHERE u.old > 10;