如何基于PostgreSQL中JSON中的另一个字符串从JSON查询字符串？ [英] How do I query a string from JSON based on another string within the JSON in PostgreSQL?

问题描述

MyTable.MyField包含以下（简化的）JSON块：

MyTable.MyField in my PostgreSQL database contains the following (simplified) JSON block:

{
    "base": {
        "fields": [
            {
                "fieldid": "c12f",
                "fieldname": "sizes",
                "choices": [
                    {
                        "choiceid": "2db3",
                        "size": "small"
                    },
                    {
                        "choiceid": "241f",
                        "size": "medium"
                    },
                    {
                        "choiceid": "3f52",
                        "size": "large"
                    }
                ],
                "answer": "241f"
            }
        ]
    }
}

如何使用 answer 的值来提取所选的 size 来自 c hoices 数组（即在这种情况下为中）？

How can I use the value of answer to extract the chosen size from the choices array please (i.e. in this case "medium")?

（注意：我已经尝试过。有关此问题的TLDR版本，请参见尝试构造PostgreSQL查询以从JSON中提取文本值对象，在数组中，在对象中，在数组中，在对象中。）

(Note: I have tried. For a TLDR version of this question see Trying to construct PostgreSQL Query to extract from JSON a text value in an object, in an array, in an object, in an array, in an object .)

谢谢。

推荐答案

您可以使用 json_array_elements 在横向联接中，然后仅查询您要查找的字段：

You can use json_array_elements in a lateral join, then just query the fields that you are looking for:

SELECT
  field -> 'fieldid' AS id,
  choice -> 'size' AS size
FROM
  my_table,
  json_array_elements(json_column -> 'base' -> 'fields') field,
  json_array_elements(field -> 'choices') choice
WHERE
  field ->> 'answer' = choice ->> 'choiceid'

这篇关于如何基于PostgreSQL中JSON中的另一个字符串从JSON查询字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！