I am new to Snowflake and I am trying to write an SQL query to create a JSON object while excluding a specific column from the output

table: lex

my codes so far

With wt as (
    Select *
    FROM lex
),
-------Convert to JSON------
wt_json as (
    select id,
            ARRAY_AGG(object_construct_keep_null(*)) withdrawals
    from wt
    Group by id
)
select *  from wt_json

this produces an output similar to this

However, I am trying to exclude the Id in the JSON object.

what I am trying to achieve

CodePudding user response：

You are using * in the object_construct_keep_null function. This means the object is constructed from from all available columns. If you don't want this then instead specify each column that you do want, in the form 'key', value. You can include the "id" column separately to achieve what you want:

WITH src AS (
    SELECT '01' AS "id", 200 AS "amount", 'accept' AS "status"
    UNION ALL 
    SELECT '01' AS "id", 100 AS "amount", 'accept' AS "status"
    UNION ALL 
    SELECT '02' AS "id", 200 AS "amount", 'accept' AS "status"
    UNION ALL 
    SELECT '02' AS "id", 100 AS "amount", 'accept' AS "status"
    UNION ALL 
    SELECT '03' AS "id", 200 AS "amount", 'accept' AS "status"
)
SELECT 
      "id"
    ,  array_agg(
          object_construct_keep_null(
                'amount', "amount", 
                'status', "status"
          )
       ) AS "withdrawals"
FROM src
GROUP BY "id"

Result:

id     withdrawals
---------------------------------------------------------------------------------------------------
01  [  {    "amount": 200,    "status": "accept"  },  {    "amount": 100,    "status": "accept"  }]
02  [  {    "amount": 200,    "status": "accept"  },  {    "amount": 100,    "status": "accept"  }]
03  [  {    "amount": 200,    "status": "accept"  }]

CodePudding user response：

So the problem with OBJECT_DELETE is the id has to be the same case as the key, as JSON is case sensitive, and yet SQL is not, and thus by default it all becomes upper case, thus in my code below given I allow id to be non-qouted, it really is 'ID' for the OBJECT_DELETE command.

thus:

WITH wt AS (
    SELECT * FROM VALUES
        ('001', 200, 'accept'),
        ('001', 100, 'accept'),
        ('002', 200, 'accept'),
        ('002', 100, 'accept'),
        ('003', 200, 'accept')
        v(id, amount, status)
)
SELECT w.id,
    ARRAY_AGG(object_construct_keep_null(*)) AS org_withdrawals,
    ARRAY_AGG(object_delete(object_construct_keep_null(*),'ID')) AS del_withdrawals,
    ARRAY_AGG(object_construct_keep_null('amount', w.amount, 'status', w.status)) AS add_withdrawals
FROM wt as w
GROUP BY 1;

If you want control of the case of your output object, I would go the OBJECT_CONSTRUST direction, as there you have independent control. But if you are concerned the table will change shape, OR there are many columns, the * - ID approach, might be the simplest, albeit less performant option.

CodePudding user response：

Using OBJECT_DELETE:

Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.

With wt as (
    Select *
    FROM  lex
 ),
-------Convert to JSON------
wt_json as (
   select id,
       ARRAY_AGG(OBJECT_DELETE(object_construct_keep_null(*), 'id')) withdrawals
   from wt
   Group by id
)
select *  from wt_json