blocks|key|4513459|text|我建议在您的情况下使用json_to_recordset命令。然后，您的SQL应为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4513460|select+*
from+json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as+x("operation"+text,+"taxCode"+int);|code-block|syntax|javascript|4513461|输出为：|4513462|------------------------
%7C+++%7Coperation%7CtaxCode+%7C
------------------------
%7C+1+%7C+++"U"+++%7C+++1000+%7C
------------------------
%7C+2+%7C+++"U"+++%7C++10001+%7C
------------------------|4513463|示例中的列(或JSON键)可以自由地进一步扩展。|4513464|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I would suggest using the json_to_recordset command in your case. Your SQL should then be:

<pre><code>select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);
</code></pre>

The output is:

<pre><code>------------------------
| |operation|taxCode |
------------------------
| 1 | "U" | 1000 |
------------------------
| 2 | "U" | 10001 |
------------------------
</code></pre>

The columns (or JSON keys) of the example can be freely further expanded.

blocks|key|1700598|text|我把最初由pozs写的答案贴在评论区。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1700599|unnest()用于PostgreSQL的数组类型。|offset|length|style|CODE|1700600|相反，可以使用以下函数之一：|1700601|1700602|json_array_elements(json)+(9.3%2B)|unordered-list-item|1700603|jsonb_array_elements(jsonb)+(9.4%2B)|1700604|json[b]_array_elements_text(json[b])+(9.4%2B)|1700605|1700606|示例|BOLD|1700607|select+*+from+json_array_elements('[1,true,+[2,false]]')|code-block|syntax|javascript|1700608|输出值|1700609|+-------------
+%7C+1+++++++++%7C
+-------------
+%7C+true++++++%7C
+-------------
+%7C+[2,false]+%7C
+-------------|1700610|可以在其中找到v9.4文档的Here。|1700611|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/9.4/static/functions-json.html^0|0|0|8|0|0|0|0|P|0|0|R|0|0|10|0|0|0|2|0|0|0|0|E|4|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@$D|1G|E|1H|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1I|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1K|8|@$D|1L|E|1M|F|G]]|9|@]|A|$]]|$1|N|3|O|5|M|7|1N|8|@$D|1O|E|1P|F|G]]|9|@]|A|$]]|$1|P|3|Q|5|M|7|1Q|8|@$D|1R|E|1S|F|G]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1U|8|@$D|1V|E|1W|F|U]]|9|@]|A|$]]|$1|V|3|W|5|X|7|1X|8|@]|9|@]|A|$Y|Z]]|$1|10|3|11|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|12|3|13|5|X|7|1Z|8|@]|9|@]|A|$Y|Z]]|$1|14|3|15|5|6|7|20|8|@]|9|@$D|21|E|22|1|23]]|A|$]]|$1|16|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|1D]]]]

I post the answer originally written by pozs in the comment section. 

<code>unnest()</code> is for PostgreSQL's array types. 

Instead one of the following function can be used:

<ul>
<li><code>json_array_elements(json)</code> (9.3+)</li>
<li><code>jsonb_array_elements(jsonb)</code> (9.4+)</li>
<li><code>json[b]_array_elements_text(json[b])</code> (9.4+)</li>
</ul>

Example:

<pre><code>select * from json_array_elements('[1,true, [2,false]]')
</code></pre>

output value 

<pre><code> -------------
 | 1 |
 -------------
 | true |
 -------------
 | [2,false] |
 -------------
</code></pre>

<a href="https://www.postgresql.org/docs/9.4/static/functions-json.html" rel="noreferrer">Here</a> where the documentation for v9.4 can be found.

blocks|key|1695625|text|更难的例子：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1695626|假设您有一个表，每个表中的行都包含jsonb数组，您希望拆分(或取消嵌套)所有这些数组，并对其中包含的记录执行一些聚合计算。|1695627|表(设为categories)：|offset|length|style|CODE|1695628|+id+%7C+specifics+(jsonb)
-----------------------------------------------------------------------------------
++1+%7C+[{"name":+"Brand",+"required":+true},+{"name":+"Color",+"required":+false}]
++2+%7C+[{"name":+"Brand",+"required":+false},+{"name":+"Color",+"required":+false}]|code-block|syntax|javascript|1695629|所以，如果你想计算你有多少需要的细节，你将需要使用这样的查询：|1695630|SELECT+specs.name,+COUNT(*)+AS+total
FROM+
++categories,+
++jsonb_to_recordset(categories.specifics)+AS+specs(name+jsonb,+required+boolean)
WHERE+
++specs.required+=+TRUE
++--+AND+any+other+restrictions+you+need
GROUP+BY+specs.name
ORDER+BY+total+DESC;|1695631|这里FROM+x,+function(x.column)是lateral+join的缩写形式，它有效地将categories中的每一行与由jsonb_to_recordset函数从jsonb数组中创建的虚拟表连接在同一行中。|1695632|结果将是：|1695633|+name++%7C+total
---------------
+Brand+%7C+++++1|1695634|链接到数据库文件：https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0|1695635|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-LATERAL|1|https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0^0|0|0|4|A|0|0|0|0|2|Q|1G|A|1X|I|T|C|0|0|0|0|9|1G|1|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@$F|1D|G|1E|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|1F|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1I|8|@$F|1J|G|1K|H|I]|$F|1L|G|1M|H|I]|$F|1N|G|1O|H|I]]|9|@$F|1P|G|1Q|1|1R]]|A|$]]|$1|U|3|V|5|6|7|1S|8|@]|9|@]|A|$]]|$1|W|3|X|5|L|7|1T|8|@]|9|@]|A|$M|N]]|$1|Y|3|Z|5|6|7|1U|8|@]|9|@$F|1V|G|1W|1|1X]]|A|$]]|$1|10|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17]]|18|$5|13|14|15|A|$16|19]]]]

More difficult example:
Suppose you have a table with rows containing jsonb array each and you wish to splat (or unnest) all that arrays and do some aggregate calculations on records contained in them.
Table (let it be <code>categories</code>):
<pre><code> id | specifics (jsonb)
-----------------------------------------------------------------------------------
 1 | [{&quot;name&quot;: &quot;Brand&quot;, &quot;required&quot;: true}, {&quot;name&quot;: &quot;Color&quot;, &quot;required&quot;: false}]
 2 | [{&quot;name&quot;: &quot;Brand&quot;, &quot;required&quot;: false}, {&quot;name&quot;: &quot;Color&quot;, &quot;required&quot;: false}]
</code></pre>
So, if you want to count, how many required specifics you have, you will need to use such query:
<pre><code>SELECT specs.name, COUNT(*) AS total
FROM 
 categories, 
 jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)
WHERE 
 specs.required = TRUE
 -- AND any other restrictions you need
GROUP BY specs.name
ORDER BY total DESC;
</code></pre>
Here <code>FROM x, function(x.column)</code> is a shortened form of a <a href="https://www.postgresql.org/docs/10/static/queries-table-expressions.html#QUERIES-LATERAL" rel="noreferrer">lateral join</a> which effectively joins every row from <code>categories</code> with virtual table created by <code>jsonb_to_recordset</code> function from jsonb array in that same row.
And result will be:
<pre><code> name | total
---------------
 Brand | 1
</code></pre>
Link to DB Fiddle: <a href="https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0" rel="noreferrer">https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0</a>

I have a json array stored in my postgres database.
The json looks like this:
<pre class="lang-none prettyprint-override"><code>[
 {
 &quot;operation&quot;: &quot;U&quot;,
 &quot;taxCode&quot;: &quot;1000&quot;,
 &quot;description&quot;: &quot;iva description&quot;,
 &quot;tax&quot;: &quot;12&quot;
 },
 {
 &quot;operation&quot;: &quot;U&quot;,
 &quot;taxCode&quot;: &quot;1001&quot;,
 &quot;description&quot;: &quot;iva description&quot;,
 &quot;tax&quot;: &quot;12&quot;
 },
 {
 &quot;operation&quot;: &quot;U&quot;,
 &quot;taxCode&quot;: &quot;1002&quot;,
 &quot;description&quot;: &quot;iva description&quot;,
 &quot;tax&quot;: &quot;12&quot;
 }
]
</code></pre>
Now I need to <code>SELECT</code> the array so that any element is in a different row of the query result. So the <code>SELECT</code> statement I perform must return the data in this way:
<pre class="lang-none prettyprint-override"><code> data
--------------------------------------------------------------------------------------
{ &quot;operation&quot;: &quot;U&quot;, &quot;taxCode&quot;: &quot;1000&quot;, &quot;description&quot;: &quot;iva description&quot;, &quot;tax&quot;:&quot;12&quot;}
{ &quot;operation&quot;: &quot;U&quot;, &quot;taxCode&quot;: &quot;1001&quot;, &quot;description&quot;: &quot;iva description&quot;, &quot;tax&quot;:&quot;12&quot;}
{ &quot;operation&quot;: &quot;U&quot;, &quot;taxCode&quot;: &quot;1002&quot;, &quot;description&quot;: &quot;iva description&quot;, &quot;tax&quot;:&quot;12&quot;}
</code></pre>
I tried using the <code>unnest()</code> function
<pre class="lang-sql prettyprint-override"><code>SELECT unnest(json_data::json)
FROM my_table
</code></pre>
but it doesn't accept the <code>jsonb</code> type.

How to turn a json array into rows in postgres

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在postgres数据库中存储了一个json数组。json如下所示：[    {        "operation": "U",        "taxCode": "1000",        "description": "iva description",        "tax": "12"    },  ...

问如何在postgres中将json数组转换为行
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在postgres中将json数组转换为行EN