时间戳

首页 > 解决方案 > 分析在 postgreSQL 中将文件上传到 API 时产生的错误

postgresql - 分析在 postgreSQL 中将文件上传到 API 时产生的错误

问题描述

我正在尝试分析当我们的客户尝试将他们的文件上传到我们的盘子表单时产生的错误，不确定这是否可能使用 PostgreSQL。

我的表有三列company_id，import_id和operation_errors。

我尝试了这个查询，它在有一个错误的行上完美运行，但在大多数情况下，每次导入我有超过 3 个错误，我的查询选择第一个只有第一个错误更多。

SELECT importable_id ,
       importable_type ,
       substring(lower(replace_operation)
                 FROM 'title:(.+?)detail:') test
FROM
  ( SELECT import_id ,
           importable_id ,
           i.importable_type ,
           operation_errors  as replace_operation
   FROM import_results ir
   JOIN imports i ON ir.import_id = i.id
   WHERE operation_errors IS NOT NULL
     AND i.created_at >= date_trunc('month', CURRENT_DATE :: date)) a
WHERE importable_type = 'Company';

正如我所说，该行可能有一个或多个错误这里是错误消息的一个示例

      ---
      - !ruby/hash:ActiveSupport::xxxxxxxxxxxxxxxx
        code: 314
      title: xxxxxxxxxxxxxxxxxxxxxxxx.
        detail: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
        xxxx. i.e 21 February, 2018.
     source: !ruby/hash:ActiveSupport::xxxxxxxxxxxxxxxxxxxxxxxxx
      pointer: "/data/attributes/xxxxxxx"
      - !ruby/hash:ActiveSupport::xxxxxxxxxxxxxxxxxxxxxxxxxxx
       code: 343
        title: xxxxxx xxxxx xxxxxxx xxxxxxxx xxxx
     detail: xxxxxx xxxxxxx xxxxxx xxxxxxxx xxxxxxxxxxxxx
        xxxxxx xxxxxxxx xxxxx xxxxxxx xxxx xxxxxxxx xxxxxxxxxxx.
     source: !ruby/hash:ActiveSupport::xxxxxxxxxxxxxxxxx
     pointer: "/data/attributes/xxxxxxxxxxx"
    - !ruby/hash:ActiveSupport::             
     code: 342
      title: xxxxx xxxxx xxxxx xxxxxxx xxxxx xxxxxxxxxx xxxxxxxxx xxx
    detail: xxxxxx xx xxx xxx xxx xxx xxxx xxxxxxx xxxxx
    xxxxxxxxx     x xxxxxxx xxxxxxx xx xxxxxx xx xx.
      source: !ruby/hash:ActiveSupport::xxxxxxxxxxx
     pointer: "/data/attributes/xxxxxxxxxx"

我希望有这样的东西

      importable_id errore1         errore 2        errore 3
       1           title: xxxxx.    title: xxxxx.   title: xxxxx.
       2           title: xxxxx.        
       3           itle: xxxxx.      title: xxxxx.

我也试过

       split_part(replace_operation, ' ', 1) AS col1

但我最终得到了 40 列，但仍然没有在某些行中得到所有错误

标签： postgresql

解决方案

我设法找到了我的问题的答案谢谢@Andyk

WITH errors AS
  (SELECT importable_id ,
          importable_type,
          created_at ,
          substring(lower(replace_operation)
                    FROM 'code:(.+?)title') code_1 ,
          split_part(replace_operation, 'code', 2) AS code_2 ,
          split_part(replace_operation, 'title', 2) AS sec_part ,
          split_part(split_part(replace_operation, 'title', 2), ':', 2) error_1 ,
          split_part(replace_operation, 'title', 3) AS third_part ,
          split_part(split_part(replace_operation, 'title', 3), ':', 2) error_2 ,
          split_part(replace_operation, 'title', 4) AS f_part ,
          split_part(split_part(replace_operation, 'title', 4), ':', 2) error_3 ,
          split_part(replace_operation, 'title', 5) AS f_part ,
          split_part(split_part(replace_operation, 'title', 5), ':', 2) error_4
   FROM
     ( SELECT import_id ,
              importable_id ,
              i.importable_type ,
              operation_errors AS replace_operation,
              i.created_at
      FROM import_results ir
      JOIN imports i ON ir.import_id = i.id
      WHERE operation_errors IS NOT NULL
        AND i.created_at >= date_trunc('month', CURRENT_DATE :: date)) a
   WHERE importable_type = 'Company')
SELECT importable_id,
       created_at::date ,
       code_1 ,
       error_1 ,
       error_2 ,
       error_3 ,
       error_4
FROM errors

推荐阅读