python - 在 Redshift 中的单独行上返回 JSON 数组列的元素

Question

我有一个 Redshift 表，其中一列中有一个 JSON 数组:

 id | metadata
---------------------------------------------------------------------------
 1  | [{"pet":"dog","country":"uk"}, {"pet":"cat","country":"us"}]
 2  | [{"pet":"cat","country":"uk"}]
 3  | []

我想要一个如下所示的表格:

 id |   pet   |  country
------------------------
 1  | dog       | uk
 1  | cat       | us
 2  | cat       | uk

有没有办法在 Redshift 表上使用 sql 命令和/或 python 用户定义函数来执行此操作

Answer 1

我对 Redshift 使用这个(丑陋的)方法。令 seq 为具有单个字段 num 的表，其中包含从 1 到 10000 的数字(假设一行中的宠物不超过 10000 只)。您可以使用 generate_series(1, 10000)，但由于奇怪的原因它速度较慢:(

select
  id,
  JSON_EXTRACT_PATH_TEXT(single_metadata, 'pet') as pet,
  JSON_EXTRACT_PATH_TEXT(single_metadata, 'country') as country
from
(
  select
    t.id,
    cast(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(t.metadata, num) as json) as single_metadata
  from 
    t join seq -- or (select num from generate_series(1, 10000))
  on num <= json_array_length(t.metadata)
)