英文:
How to get complete record element from PostgreSQL json_to_recordset() Function?
问题
我想从 json_to_recordset() 中提取完整的记录元素。如何获取它。
以下是我的问题描述：
描述
我有一个带有 JSON 类型的列，其值如下，其中 JSON 数组 的每个元素不一定具有相同的格式。我想将此 JSON 数组转换为表行。
我已尝试使用可用的 json_to_recordset() 函数，我也能够提取单个行的元素，如 id 或 name 等，但无法提取完整的行。
[{"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34},
{"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}]
我的用例需要将完整的行存储为单列，如何在 PostgreSQL 中实现它。
以下是我为单个字段应用的逻辑
SELECT json_to_recordset( data::json) as r( id text, name text)
输出 我得到的是：
期望的输出
英文:
I want to extract the complete record elements from json_to_recordset(). How to get it.
Below is the description of my problem statement:
Description
I have one column with JSON Type and with the below value where each element of JSON Array are not necessarily has the same format. I want to convert this JSON Array into Table rows.
I have tried json_to_recordset() function available and I am also able to extract single elements from a row such as an id or name etc but not complete row.
[{"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34},
{"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}]
My use case require to store the complete row as a single column how to get it in postgres.
Below logic, I have applied for a single field
SELECT json_to_recordset( data::json) as r( id text, name text)
The output I am getting:
Expected Output
答案1
得分: 1
你可以使用 json_array_elements 将JSON数组展开为一组JSON值：
->> 用于获取JSON对象字段作为文本
select row->>'id' as id, row->>'name' as name, row
FROM mytable, json_array_elements(myjson) row;
结果：
id name row
1 AV {"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34}
2 Ram {"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}
英文:
You can do it using json_array_elements to Expands a JSON array to a set of JSON values :
->> to get JSON object field as text
select row->>'id' as id, row->>'name' as name, row
FROM mytable, json_array_elements(myjson) row;
Result :
id name row
1 AV {"id":"1","type":"user","name":"AV","displayName":"Aviral","Company":"ABC","Age":34}
2 Ram {"id":"2","type":"user","name":"Ram","displayName":"Ram","City":"Jaipur"}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论