CODEWITHSUNDEEP
sqljsonamazon-web-servicesamazon-redshift
Novice
Novice

Reputation: 443

How to merge JSON objects in Redhift?

Let's say I have two rows

Account | usage | usage by user      | usage month
1       | 20    |  { "a":10, "b":10} | Jan
1       | 25    |  { "a": 15, "c":10}| Feb

I want to write a query to get the total usage as

1       | 45    | { "a" : 25, "b" :10, "c":10}

I can get the usage by using SUM and group by Account. But I'm not sure on how to merge the json objects in Redshift. Any pointers on how to do this?

Upvotes: 1

Views: 602

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270184

There is no native way to do this in Amazon Redshift. Also, I would recommend that you do NOT do this in Redshift.

JSON fields are difficult to interpret within Redshift. You would need to use a function like JSON_EXTRACT_PATH_TEXT to retrieve elements from JSON. It is quite inefficient and does not take advantage of the capabilities of Redshift to optimize queries.

If you are keeping such a field to hold 1-to-many value relationships, it would be better to create a separate usage table that holds the usage information per account and user. Then, then join to that table in your queries. This is normal SQL — nothing specifically to do with Redshift.

For example, see: Creating multiple tables and table relationships

Upvotes: 2

Related Questions