How to get array of objects in SQL with own key name? [duplicate]

Question

I want to select some categories and sub categories and want to show it all in one array like this:

[
 {
   category: 'ELECTRONICS',
   sub_category: 'CONSOLE'
 }
]

but I got this:

[
    { f1: 'ELECTRONICS', f2: 'CONSOLE' },
    { f1: 'ELECTRONICS', f2: 'CONSOLE' }
  ]

here is a code snippet

      SELECT 

      s.id,
      s.name,
      s.image,
      s.verified,
      s.s_position,
      s.status,
      s.platform,
      s.coupon_name,
      s.coupon_pct,
      s.coupon_start,
      s.coupon_end,

      json_agg(row(c.category, scg.sub_category)) as categories

....

Have you tried json_build_object? postgresql.org/docs/9.5/functions-json.html — acurtis166
– acurtis166, Commented Sep 13, 2023 at 2:44
@acurtis166 I just don't believe you really use version 9.5, please use the "current" version to point to the most recent production version: postgresql.org/docs/current/functions-json.html — Frank Heikens
– Frank Heikens, Commented Sep 13, 2023 at 3:20

Erwin Brandstetter · Accepted Answer · 2023-09-13 03:51:59Z

0

SELECT ...
     , json_agg(json_build_object('category', c.category
                                , 'sub_category', scg.sub_category)) as categories
...

See:

Return multiple columns of the same row as JSON array of objects

answered Sep 13, 2023 at 3:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get array of objects in SQL with own key name? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related