问题

Here is the translated content you requested:

{
    "BestBuy.com": {
        "data": [
            [24.99, "2023-04-08 00:20:13.756603+00"],
            [39.99, "2023-04-14 22:57:56.003837+00"],
            [39.99, "2023-04-15 02:42:55.190658+00"],
            [39.99, "2023-04-15 16:57:55.045572+00"],
            [39.99, "2023-04-15 18:42:53.294265+00"],
            [24.99, "2023-04-08 02:57:51.220209+00"],
            [24.99, "2023-04-08 03:12:50.504241+00"],
            [24.99, "2023-04-08 05:57:50.356346+00"],
            [24.99, "2023-04-08 07:27:52.21909+00"],
            [39.99, "2023-04-16 00:42:52.578908+00"],
            [39.99, "2023-04-16 19:12:55.789055+00"],
            [34.99, "2023-04-25 00:57:55.631403+00"],
            [24.99, "2023-04-08 00:57:51.861773+00"],
            [24.99, "2023-04-08 02:27:54.083473+00"],
            [24.99, "2023-04-08 14:57:51.008387+00"],
            [39.99, "2023-04-18 11:57:54.997804+00"],
            [39.99, "2023-04-18 05:42:55.248361+00"],
            [39.99, "2023-04-18 12:12:54.926158+00"],
            [34.99, "2023-04-28 21:43:07.330042+00"],
            [34.99, "2023-04-25 23:12:58.103744+00"],
            [34.99, "2023-04-28 21:58:06.652304+00"],
            [39.99, "2023-04-20 06:42:55.641028+00"],
            [34.99, "2023-04-21 23:57:57.777371+00"],
            [34.99, "2023-04-20 15:12:56.185695+00"],
            [34.99, "2023-04-21 21:27:58.06414+00"],
            [34.99, "2023-04-22 04:27:58.140495+00"],
            [34.99, "2023-04-28 23:28:07.284809+00"],
            [34.99, "2023-04-29 00:58:06.053093+00"],
            [24.99, "2023-04-09 17:12:52.552401+00"],
            [34.99, "2023-04-22 01:27:58.035003+00"],
            [34.99, "2023-04-26 07:57:57.272001+00"],
            [34.99, "2023-04-22 00:27:56.772623+00"],
            [34.99, "2023-04-21 05:57:57.145709+00"],
            [34.99, "2023-04-29 03:28:06.034023+00"],
            [39.99, "2023-04-10 05:12:52.093764+00"],
            [34.99, "2023-04-21 21:57:59.336833+00"],
            [34.99, "2023-04-21 22:27:58.280576+00"],
            [34.99, "2023-04-28 21:28:05.418201+00"],
            [39.99, "2023-04-11 21:27:53.44626+00"],
            [34.99, "2023-04-22 15:27:56.237947+00"],
            [34.99, "2023-04-23 20:27:57.577541+00"],
            [39.99, "2023-04-13 13:57:52.456967+00"],
            [39.99, "2023-04-14 01:27:55.291552+00"],
            [39.99, "2023-04-14 21:27:58.342033+00"],
            [39.99, "2023-04-14 21:57:57.407943+00"],
            [34.99, "2023-04-29 06:43:06.139272+00"],
            [34.99, "2023-04-29 08:13:04.733555+00"],
            [34.99, "2023-04-29 13:13:06.21473+00"],
            [34.99, "2023-04-29 18:28:04.24191+00"],
            [19.99, "2023-04-30 05:43:02.277314+00"],
            [19.99, "2023-05-03 21:43:03.169792+00"],
            [19.99, "2023-05-08 20:13:07.710327+00"],
            [19.99, "2023-05-08 21:58:07.183089+00"],
            [19.99, "2023-05-10 07:13:07.101684+00"]
        ]
    },
    "Target.com": {
        "data": [
            [39.99, "2023-04-11 00:53:57.742973+00"],
            [39.99, "2023-04-14 03:15:01.237546+00"],
            [39.99, "2023-04-13 23:45:03.201827+00"],
            [39.99, "2023-04-14 00:00:01.635091+00"],
            [33.99, "2023-05-03 02:11:36.148637+00"],
            [33.99, "2023-05-03 02:17:06.085251

<details>
<summary>英文:</summary>

        CREATE TABLE &quot;Logs&quot;(
        id bigint NOT NULL,
        product_id bigint,
        product_price numeric,
        product_store text
        last_updated timestamp with time zone DEFAULT now()
        );
    
    INSERT into &quot;Logs&quot; VALUES(2095, 13, 249.99, &#39;BestBuy.com&#39;, &#39;2023-04-08 00:20:12.199251+00&#39;);
    INSERT into &quot;Logs&quot; VALUES(2096, 13, 249.99, &#39;Target.com&#39;, &#39;2023-04-08 00:20:12.199251+00&#39;);

Query

    SELECT json_object(array_agg(product_store), array_agg(json::text))as price_log FROM (
    SELECT product_store, array_agg(array[&quot;Logs&quot;.product_price::text, &quot;Logs&quot;.last_updated::text]) as json
                         FROM &quot;Logs&quot;
                         WHERE &quot;Logs&quot;.product_id = 13
                         GROUP BY &quot;Logs&quot;.product_store
    ) as price_log

Output

    {
       &quot;BestBuy.com&quot;:&quot;{{24.99,\&quot;2023-04-08 00:20:13.756603+00\&quot;},{39.99,\&quot;2023-04-14 22:57:56.003837+00\&quot;},{39.99,\&quot;2023-04-15 02:42:55.190658+00\&quot;},{39.99,\&quot;2023-04-15 16:57:55.045572+00\&quot;},{39.99,\&quot;2023-04-15 18:42:53.294265+00\&quot;},{24.99,\&quot;2023-04-08 02:57:51.220209+00\&quot;},{24.99,\&quot;2023-04-08 03:12:50.504241+00\&quot;},{24.99,\&quot;2023-04-08 05:57:50.356346+00\&quot;},{24.99,\&quot;2023-04-08 07:27:52.21909+00\&quot;},{39.99,\&quot;2023-04-16 00:42:52.578908+00\&quot;},{39.99,\&quot;2023-04-16 19:12:55.789055+00\&quot;},{34.99,\&quot;2023-04-25 00:57:55.631403+00\&quot;},{24.99,\&quot;2023-04-08 00:57:51.861773+00\&quot;},{24.99,\&quot;2023-04-08 02:27:54.083473+00\&quot;},{24.99,\&quot;2023-04-08 14:57:51.008387+00\&quot;},{39.99,\&quot;2023-04-18 11:57:54.997804+00\&quot;},{39.99,\&quot;2023-04-18 05:42:55.248361+00\&quot;},{39.99,\&quot;2023-04-18 12:12:54.926158+00\&quot;},{34.99,\&quot;2023-04-28 21:43:07.330042+00\&quot;},{34.99,\&quot;2023-04-25 23:12:58.103744+00\&quot;},{34.99,\&quot;2023-04-28 21:58:06.652304+00\&quot;},{39.99,\&quot;2023-04-20 06:42:55.641028+00\&quot;},{34.99,\&quot;2023-04-21 23:57:57.777371+00\&quot;},{34.99,\&quot;2023-04-20 15:12:56.185695+00\&quot;},{34.99,\&quot;2023-04-21 21:27:58.06414+00\&quot;},{34.99,\&quot;2023-04-22 04:27:58.140495+00\&quot;},{34.99,\&quot;2023-04-28 23:28:07.284809+00\&quot;},{34.99,\&quot;2023-04-29 00:58:06.053093+00\&quot;},{24.99,\&quot;2023-04-09 17:12:52.552401+00\&quot;},{34.99,\&quot;2023-04-22 01:27:58.035003+00\&quot;},{34.99,\&quot;2023-04-26 07:57:57.272001+00\&quot;},{34.99,\&quot;2023-04-22 00:27:56.772623+00\&quot;},{34.99,\&quot;2023-04-21 05:57:57.145709+00\&quot;},{34.99,\&quot;2023-04-29 03:28:06.034023+00\&quot;},{39.99,\&quot;2023-04-10 05:12:52.093764+00\&quot;},{34.99,\&quot;2023-04-21 21:57:59.336833+00\&quot;},{34.99,\&quot;2023-04-21 22:27:58.280576+00\&quot;},{34.99,\&quot;2023-04-28 21:28:05.418201+00\&quot;},{39.99,\&quot;2023-04-11 21:27:53.44626+00\&quot;},{34.99,\&quot;2023-04-22 15:27:56.237947+00\&quot;},{34.99,\&quot;2023-04-23 20:27:57.577541+00\&quot;},{39.99,\&quot;2023-04-13 13:57:52.456967+00\&quot;},{39.99,\&quot;2023-04-14 01:27:55.291552+00\&quot;},{39.99,\&quot;2023-04-14 21:27:58.342033+00\&quot;},{39.99,\&quot;2023-04-14 21:57:57.407943+00\&quot;},{34.99,\&quot;2023-04-29 06:43:06.139272+00\&quot;},{34.99,\&quot;2023-04-29 08:13:04.733555+00\&quot;},{34.99,\&quot;2023-04-29 13:13:06.21473+00\&quot;},{34.99,\&quot;2023-04-29 18:28:04.24191+00\&quot;},{19.99,\&quot;2023-04-30 05:43:02.277314+00\&quot;},{19.99,\&quot;2023-05-03 21:43:03.169792+00\&quot;},{19.99,\&quot;2023-05-08 20:13:07.710327+00\&quot;},{19.99,\&quot;2023-05-08 21:58:07.183089+00\&quot;},{19.99,\&quot;2023-05-10 07:13:07.101684+00\&quot;}}&quot;,
       &quot;Target.com&quot;:&quot;{{39.99,\&quot;2023-04-11 00:53:57.742973+00\&quot;},{39.99,\&quot;2023-04-14 03:15:01.237546+00\&quot;},{39.99,\&quot;2023-04-13 23:45:03.201827+00\&quot;},{39.99,\&quot;2023-04-14 00:00:01.635091+00\&quot;},{33.99,\&quot;2023-05-03 02:11:36.148637+00\&quot;},{33.99,\&quot;2023-05-03 02:17:06.085251+00\&quot;}}&quot;
    }

Maybe I am going about this the wrong way, I am trying to combine the rows of &quot;Logs&quot; that are associated with a Product ID, since this is being used in a Subquery I need this query to be in an array since there are times where there are more than one result, like above. 

My desired result is: 

        {
    	&quot;BestBuy.com&quot;: {
    		&quot;data&quot;: [
    			[24.99, &quot;2023-04-08 00:20:13.756603+00&quot;],
    			[24.99, &quot;2023-04-08 00:20:13.756603+00&quot;]
    		]
    	},
    	&quot;Target.com&quot;: {
    		&quot;data&quot;: [
    			[24.99, &quot;2023-04-08 00:20:13.756603+00&quot;],
    			[24.99, &quot;2023-04-08 00:20:13.756603+00&quot;]
    		]
    	}
    }



</details>


# 答案1
**得分**: 0

Here's the translated code:

```sql
-- 最终 JSON
with tmp as (
    select json_build_object(product_store, json_build_object('Data', json_agg(t.a))) json_row from (
      select
        product_store, json_build_array(product_price,last_updated) a
        from "Logs" 
    ) t
    group by product_store)
select json_object_agg(key, value) as result
from   tmp, json_each(json_row);

Output:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 { "BestBuy.com" : {"Data" : [[249.99, "2023-04-08T00:20:12.199251+00:00"], [249.99, "2023-04-09T00:20:12.199251+00:00"], [249.99, "2023-04-10T00:20:12.199251+00:00"]]}, "Target.com" : {"Data" : [[249.99, "2023-04-08T00:20:12.199251+00:00"], [249.99, "2023-04-11T00:20:12.199251+00:00"], [249.99, "2023-04-12T00:20:12.199251+00:00"]}}
(1 row)

You can view the dbfiddle link for reference.

英文:

-- final JSON
with tmp as (
select json_build_object(product_store, json_build_object(&#39;Data&#39;, json_agg(t.a))) json_row from (
select
product_store, json_build_array(product_price,last_updated) a
from &quot;Logs&quot; 
) t
group by product_store)
select json_object_agg(key, value) as result
from   tmp, json_each(json_row);

Details:

Inner query constructs an array from 2 columns per row using function<br>
json_build_array(product_price, last_updated)
Function json_agg concatenates all the arrays into one array grouping by product_store.
The json_build_object function builds the output json.
Finally, json_object_agg with json_each sort of concatenate json_rows into the final output.

Output:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{ &quot;BestBuy.com&quot; : {&quot;Data&quot; : [[249.99, &quot;2023-04-08T00:20:12.199251+00:00&quot;], [249.99, &quot;2023-04-09T00:20:12.199251+00:00&quot;], [249.99, &quot;2023-04-10T00:20:12.199251+00:00&quot;]]}, &quot;Target.com&quot; : {&quot;Data&quot; : [[249.99, &quot;2023-04-08T00:20:12.199251+00:00&quot;], [249.99, &quot;2023-04-11T00:20:12.199251+00:00&quot;], [249.99, &quot;2023-04-12T00:20:12.199251+00:00&quot;]]} }
(1 row)

See dbfiddle link.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Postgres将行合并为一个数组，带有条件分组子查询。

问题

如何使用Jackson定义2级继承结构

从PostgreSQL的jsonb数据中选择内部json中的数据。

JavaScript: counting strings that begin with 'A' within an array – why is my code not counting them all?

连接到远程 Linux 服务器上的 PSQL 时出现问题

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论