sql - 从 Amazon Redshift 中的 json 数组中提取特定键
问题描述
背景
我正在使用 SQL 在 Amazon Redshift 数据库中工作。我有一个表,其中一个名为属性的列包含如下所示的数据:
[{"name": "Color", "value": "Beige"},{"name":"Size", "value":Small"}]
或者
[{"name": "Size", "value": "Small"},{"name": "Color", "value": "Blue"},{"name": "Material", "value": "Cotton"}]
据我了解,以上是 JSON 字符串中的一系列路径元素。
问题
我希望提取每个 JSON 字符串中的颜色值。我不确定如何进行。我知道如果颜色在同一个位置,我可以使用索引来指示从哪里提取。但这里不是这样。
我试过的
select json_extract_array_element_text(attributes, 1) as color_value, json_extract_path_text(color_value, 'value') as color from my_table
此查询适用于某些列,但并非所有列,因为颜色值的位置不同。
我会很感激这里的任何帮助,因为我对 sql 非常陌生,并且只进行了基本的查询。我一直在使用以下页面作为参考
解决方案
首先,您的数据是数组格式(在 [ ] 之间),而不是对象格式(在 { } 之间)。您提到的页面是用于从 JSON 对象而非数组中提取数据的函数。数组格式也带来了挑战,因为您需要知道要提取的元素的数字位置。
根据您的示例数据,对象似乎是要走的路。如果是这样,您将希望重新格式化您的数据,使其更像:
{"Color": "Beige", "Size": "Small"}
和
{"Size": "Small", "Color": "Blue", "Material": "Cotton"}
仅当“名称”值在您的数据中是唯一的时,此转换才有效。
有了这个你选择的函数 - JSON_EXTRACT_PATH_TEXT() - 将从数据中提取你想要的值。
现在更改数据可能不是一种选择,处理这些数组会使事情变得更加困难和性能降低。为此,您需要通过与一组数字交叉连接来扩展这些数组,这些数字包含数组最大长度内的所有数字。例如,对于您提供的示例,您需要通过值 0、1、2 进行交叉连接,以便可以完全提取 3 元素数组。然后,您可以只过滤那些“名称”为“颜色”的行。
从数组中提取元素所需的函数是 JSON_EXTRACT_ARRAY_ELEMENT_TEXT() ,由于数组中存储了对象,因此需要对结果运行 JSON_EXTRACT_PATH_TEXT() 。
推荐阅读
- python - 在 wait_for 检查 discord.py 中检查谁对消息做出了反应
- python - 优化 Pandas 中值的插值
- c++ - 在 QSqlRelationalTableModel 中过滤 QSqlRelation
- c# - .Net 错误未正确反序列化
- bash - 当字符串有一些引号“”时,如何使用bash脚本命令xargs删除重复项?
- javascript - 在 gatsby-browser 中使用 GraphQL 数据?
- html - CSS动画仅适用于祖父母而不是父母?
- javascript - 弹出窗口不动态更新
- laravel - Laravel:如何在 MySQL 数据库中插入嵌套列表?
- python - Python/MySQL SELECT、Update 和 Fetch 在同一个查询中