How do you display multiple results from query as JSON?-CodePudding

Below is the code to get data from Sqlite and display it as JSON.

service.js

const getSchedule = async ( operatorId ) => {
    const text = `
        SELECT "a.businessName"
            , "b.opTitle"
            , b.pay
            , "b.startTime"
            , "b.endTime"
            , "a.addressLine1"
            , "a.addressLine2"
            , "a.city"
            , "a.state"
            , "a.zip"
        from businesses a join ops b
        on a.id=b.businessId where b.operatorId= $1;
    `;
    const [ schedule ] = await query( text, [ operatorId ] );
    return schedule;
    
};

The above query works correctly on DB browser for sqlite. Below is the way I use this to send data as result.

Router.js:

.get( 
        '/:operatorId/schedules'
        , async ( req, res ) => {
            const operatorId = req.params.operatorId;
            const schedule = await getSchedule( operatorId );
            console.log(schedule)
            return res
                .status( 200 )
                .json( schedule )
        }
    )

The SQL query returns two records, how do I display this as JSON data because when I try the above code I get the below output:

{
  '"a.businessName"': 'a.businessName',
  '"b.opTitle"': 'b.opTitle',
  pay: 200,
  '"b.startTime"': 'b.startTime',
  '"b.endTime"': 'b.endTime',
  '"a.addressLine1"': 'a.addressLine1',
  '"a.addressLine2"': 'a.addressLine2',
  '"a.city"': 'a.city',
  '"a.state"': 'a.state',
  '"a.zip"': 'a.zip'
}

I know this is not the right way to do this and an alternate way is to get the data normally and manipulate it later in the data function or router, but is there a way to do it this way and get the expected result which is:

[
    {
        "businessName": "Tesla"
        , "opTitle": "Replacing Tires"
        , "pay": 200.00
        , "startTime": "2021-10-21 10:00:00"
        , "endTime": "2021-10-21 15:55:00"
        , "addressLine1": "Something"
        , "addressLine2": "Line 2"
        , "city": "Dallas"
        , "state": "TX"
        , "zip": "7000"
    }
    , {
        "businessName": "Goodwill"
        , "opTitle": "Moving boxes of clothes"
        , "pay": 105.50
        , "startTime": "2021-10-22 10:00:00"
        , "endTime": "2021-10-22 15:55:00"
        , "addressLine1": "Something"
        , "addressLine2": "Line 2"
        , "city": "Irving"
        , "state": "TX"
        , "zip": "7000"
    }
]

Thank you

CodePudding user response：

Use SQLite's JSON functions to aggregate:

SELECT JSON_GROUP_ARRAY( 
         JSON_OBJECT(
           'businessName', b.businessName,
           'opTitle', o.opTitle,
           'pay', o.pay,
           'startTime', o.startTime,
           'endTime', o.endTime,
           'addressLine1', b.addressLine1,
           'addressLine2', b.addressLine2,
           'city', b.city,
           'state', b.state,
           'zip', b.zip
         )
       ) AS result
FROM businesses b JOIN ops o
ON b.id = o.businessId 
WHERE o.operatorId= $1;

See the demo.