json - PostgreSQL:JSON 字段是数据类型文本，我需要嵌套值

Question

我将以下数据存储在指定为文本而非 JSON 的 PostgreSQL 字段中:

{"description_translated": "{\"en\": \"Description in English\", \"fr\": \"Descriptif en français\"}", "name_translated": "{\"en\": \"Name in English\", \"fr\": \"Nom en français\"}", "resource_lang": "English"}

应用程序使用将 JSON 存储为文本字段的字段设置数据库。

我知道我可以提取一些“顶级”的东西，比如“resource_lang”:

Select extras::json -> 'resource_lang'

但是如果我想获取“description_translated”的英文字符串，这些都不起作用，都返回 null:

select extras::json -> 'description_translated' ->> 'en' as desc_en

select extras::json -> 'description_translated' -> 'en' as desc_en

我尝试过像这样的 cockamamie 东西:

select extras::json -> 'description_translated'::json -> 'en' as desc_en

当然这是行不通的，那么有没有办法让我的嵌套值出来呢？

我想要输出的是 desc_en = "Description in English"

Answer 1

由于 json 转换错误，您的文本字段以无效/错误格式的 json 结尾，但可以通过一些替换来修复，例如:

select
regexp_replace( 
regexp_replace(
'{"description_translated": "{\"en\": \"Description in English\", \"fr\": \"Descriptif en français\"}", "name_translated": "{\"en\": \"Name in English\", \"fr\": \"Nom en français\"}", "resource_lang": "English"}'
,'\\','','g'),'"({)|(})"','\1\2','g')::json -> 'description_translated' ->> 'en';

这样它就会返回你的值“Description in English”

Fiddle here