主页 / 问题 / 1451294
Accepted
Alex
Asked:2022-09-24 18:01:09 +0800 CST2022-09-24 18:01:09 +0800 CST

postgresql：返回和排序json部分

  • 772

如果有这样的要求

SELECT res FROM main WHERE id = 3;

以这种 json 结构的形式返回数据（res - 文本字段），例如，

 {
 "abc": {
        "a": {
             "x": {"p": "val"},
             "y": {"p": "msg", "b": 33, "ms": 0},
             "z": {"p": "msg ", "b": 12, "ms": 0}
        },
        "b": ["x", "y", "z"], 
        "c": [
             [69, "text_05", "m n o"],
             [4, " text_02", "def"],
             [1, "text_01", "abc "],
             [48, " text_04 ", "jkl"],
             [5, " text_03 ", " ghi"],
             [82, "text_06  ", " pq r "]
             ]
 }
 }

然后通过什么查询，您可以获得并仅对以下内容进行排序：

        [1, "text_01", "abc "],
        [4, " text_02", "def"],
        [5, " text_03 ", " ghi"],
        [48, " text_04 ", "jkl"],
        [69, "text_05", "m n o"],
        [82, "text_06  ", " pq r "]

我尝试： from json_to_recordset .. as x(int,text,text)，或者： SELECT res::json->'abc'->'c' FROM main WHERE id = 3 ORDER BY .. ; - 不工作。

upd1：我可以这样做，但没有排序：

SELECT res::json->'abc'->'c' FROM main WHERE id = 3;
получаю:
             [
             [69, "text_05", "m n o"],
             [4, " text_02", "def"],
             [1, "text_01", "abc "],
             [48, " text_04 ", "jkl"],
             [5, " text_03 ", " ghi"],
             [82, "text_06  ", " pq r "]
             ]
json

0 个回答

  1. Best Answer
    select json_agg(rec order by (rec->>0)::int)
  from (
    select json_array_elements(res::json->'abc'->'c') rec
      from main WHERE id = 3
  ) x

    这是如果您还希望在输出中使用 json。如果是单独的记录，那就更简单了，不需要json_agg，在通常的选择中添加order by(res::json->'abc'->'c'->>0)::int并根据需要按列扩展就足够了。

    • 1

相关问题