How to Get list of ids from same query when returning as JSON

Question

apps table

app_id  |   app_name   |  app_status
-------------------------------------
  1         scheduling    INACTIVE
  2         call          ACTIVE
  3         billing       ACTIVE
  4         order         ACTIVE

I have a query,

SELECT * FROM apps WHERE app_status = "ACTIVE" FOR JSON PATH;

This query has an app_id field. I also want a field to accompany the json output with a field ids with all the app_id as a list.

So instead of:

[{
    "app_id": 2,
    "app_name": "call",
    "status": "ACTIVE"
}, {
    "app_id": 3,
    "app_name": "billing",
    "status": "ACTIVE"
}, {
    "app_id": 4,
    "app_name": "order",
    "status": "ACTIVE"
}]

I would have:

[{
    "apps": [{
        "app_id": 2,
        "app_name": "call",
        "status": "ACTIVE"
    }, {
        "app_id": 3,
        "app_name": "billing",
        "status": "ACTIVE"
    }, {
        "app_id": 4,
        "app_name": "order",
        "status": "ACTIVE"
    }],
    "ids": [2, 3, 4]
}]

Why Microsoft don't have a simple solution for this is beyond me, it seems to be a common requirement. Some version of `JSON_QUERY( STUFF( ... FOR XML PATH('') ) )` is popular, such as this previous [StackOverflow Answer](https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016). — AlwaysLearning, Sep 15 '20 at 21:22

score 3 · Accepted Answer · answered Sep 15 '20 at 21:32

This returns the correct JSON

Data

drop table if exists dbo.tTable;
go
create table dbo.tTable(
  app_id            int not null,
  [app_name]        nvarchar(200),
  app_status        nvarchar(200));

insert dbo.tTable(app_id, app_name, app_status) values
(1, 'scheduling', 'INACTIVE'),
(2, 'call', 'ACTIVE'),
(3, 'billing', 'ACTIVE'),
(4, 'order', 'ACTIVE');

Query

with active_cte as (
    select * FROM tTable WHERE app_status = 'ACTIVE')
SELECT 
  (select * FROM active_cte FOR JSON PATH) apps,
  (select JSON_QUERY(concat('[',string_agg(app_id, ','),']'),'$') from active_cte) ids
for json path;

Output

[
  {
    "apps": [
      {
        "app_id": 2,
        "app_name": "call",
        "app_status": "ACTIVE"
      },
      {
        "app_id": 3,
        "app_name": "billing",
        "app_status": "ACTIVE"
      },
      {
        "app_id": 4,
        "app_name": "order",
        "app_status": "ACTIVE"
      }
    ],
    "ids": [ 2, 3, 4 ]
  }
]

How to Get list of ids from same query when returning as JSON

1 Answers1