I have a field called "filter" with json array like:

[
   {
      "after":"2021-10-14T00:00:00",
      "column":"createddate",
      "from":null,
      "gt":null,
      "gte":null,
      "id":"928d57b0",
      "lt":null,
      "lte":null,
      "not":null,
      "prior":null,
      "to":null,
      "value":null
   },
   {
      "after":null,
      "column":"last_coach_date",
      "from":"",
      "gt":null,
      "gte":null,
      "id":"01704cdd",
      "lt":null,
      "lte":null,
      "not":null,
      "prior":null,
      "to":"",
      "value":null
   }
]

I'm trying to extract key/value pairs where value is not null/blank and create another field for it.

Maybe to something like this or one long string:

{ "after": "2021-10-14T00:00:00", "column": "createddate", "id": "928d57b0"}, {"column": "last_coach_date","id": "01704cdd"} AS filter_value

CodePudding user response：

Using FLATTEN and OBJECT_AGG:

CREATE OR REPLACE TABLE t(col VARIANT)
AS
SELECT PARSE_JSON('[ { "after": "2021-10-14T00:00:00", "column": "createddate", "from": null, "gt": null, "gte": null, "id": "928d57b0", "lt": null, "lte": null, "not": null, "prior": null, "to": null, "value": null }, { "after": null, "column": "last_coach_date", "from": "", "gt": null, "gte": null, "id": "01704cdd", "lt": null, "lte": null, "not": null, "prior": null, "to": "", "value": null }]'
                );

Query:

SELECT s.index, OBJECT_AGG(s2.key, s2.value) AS output
FROM t
,LATERAL FLATTEN(input => col) s
,LATERAL FLATTEN(input =>  s.value) s2
WHERE NOT IS_NULL_VALUE(s2.value)  AND s2.value::TEXT != ''
GROUP BY s.INDEX;

Output:

CodePudding user response：

For Postgres:

select (select jsonb_object_agg(key, value)
        from jsonb_array_elements(t.filter) as x(item)
          cross join jsonb_each_text(x.item) as e(key,value)
        where e.value <> '')
from the_table t;

CodePudding user response：

So very similar to Lukasz solution, but OBJECT_AGG will filter out any NULL values, so if you cast to ::text the nulls become real nulls, and are filtered out, so the WHERE clause is not required:

WITH data_table AS(
  SELECT parse_json('[ { "after": "2021-10-14T00:00:00", "column": "createddate", "from": null, "gt": null, "gte": null, "id": "928d57b0", "lt": null, "lte": null, "not": null, "prior": null, "to": null, "value": null }, { "after": null, "column": "last_coach_date", "from": "", "gt": null, "gte": null, "id": "01704cdd", "lt": null, "lte": null, "not": null, "prior": null, "to": "", "value": null } ]') as json
)
SELECT f.index as batch_id,
    OBJECT_AGG(p.key, NULLIF(p.value::text,'')::variant) as obj
FROM data_table t,
  TABLE(FLATTEN(input=>t.json)) as f,
  TABLE(FLATTEN(input=>f.value)) as p
GROUP BY 1
ORDER BY 1
;

gives:

It's not clear from the question if you want 2 rows, or a single value, the latter can be had by adding another layer of aggregation via ARRAY_AGG:

WITH data_table AS(
  SELECT parse_json('[ { "after": "2021-10-14T00:00:00", "column": "createddate", "from": null, "gt": null, "gte": null, "id": "928d57b0", "lt": null, "lte": null, "not": null, "prior": null, "to": null, "value": null }, { "after": null, "column": "last_coach_date", "from": "", "gt": null, "gte": null, "id": "01704cdd", "lt": null, "lte": null, "not": null, "prior": null, "to": "", "value": null } ]') as json
)
SELECT array_agg(obj) as single_answer
FROM (
  SELECT f.index as batch_id,
      OBJECT_AGG(p.key, NULLIF(p.value::text,'')::variant) as obj
  FROM data_table t,
    TABLE(FLATTEN(input=>t.json)) as f,
    TABLE(FLATTEN(input=>f.value)) as p
  GROUP BY 1
)
;

gives: