Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL to JSON Need Bracket for Array that can have multiple rows

I’m trying to take a SQL query and turn it into JSON for an API call. I have two groupings that need to contain an opening and closing bracket [] because they can contain multiple rows. Those are shown here as [fulfillments] & [shipments]. I have it formatted correctly below

{
    "customer_order": {
        "order_number": "12394334"
    },
    "fulfillments": [
        {
            "fulfillment_id": "12394334"
        }
    ],
    "destination": {
        "contact": {
            "person_name": "Johnny Pops",
            "phone_number": 777777777,
            "email_address": "[email protected]"
        },
        "address": {
            "street_line1": "1708 Johnny Pops DRIVE",
            "city": "AUSTIN",
            "postal_code": 78745,
            "state_or_province_code": "TX",
            "country_code": "US"
        }
    },
    "shipments": [
        {
            "shipment_id": "BLDT11121",
            "fulfillment_id": "BLDT11121",
            "tracking_number": "BLDT11121",
            "carrier_scac": "SSSS"
        }
   ]
}

Using a FOR JSON PATH, WITHOUT_ARRAY_WRAPPER works but I can’t get those brackets in the proper place:

SELECT 
    [Order Number] AS [customer_order.order_number], [Order Number] AS [fulfillments.fulfillment_id]
    ,[Last Name] AS [destination.contact.person_name], [Home Phone] AS [destination.contact.phone_number], email AS [destination.contact.email_address]
    ,[Address] AS [destination.address.street_line1], [city] AS [destination.address.city], zip AS [destination.address.postal_code]
    ,[State] AS [destination.address.state_or_province_code], 'US' AS [destination.address.country_code]
    ,[Order Idwaybill] AS [shipments.shipment_id], [Order Idwaybill] AS [shipments.fulfillment_id], [Order Idwaybill] AS [shipments.tracking_number]
    ,'SEKW' AS [shipments.carrier_scac]
FROM vw__orders
WHERE [Order Number] = '12394334'

If I do a bunch of REPLACE() I can get it to work but I was curious if there was syntax I’m missing?

REPLACE(SQL, '"fulfillments":{"', '"fulfillments":[{"')
Tags:

2

Answers


  1. 0

    You can just use nested FOR JSON PATH clauses without a FROM.

    SELECT 
    [Order Number] AS [customer_order.order_number],
    (
        SELECT
            [Order Number] AS fulfillment_id
        FOR JSON PATH
    ) AS fulfillments,
    [Last Name] AS [destination.contact.person_name],
    [Home Phone] AS [destination.contact.phone_number],
    email AS [destination.contact.email_address],
    Address AS [destination.address.street_line1],
    city AS [destination.address.city],
    zip AS [destination.address.postal_code],
    State AS [destination.address.state_or_province_code],
    'US' AS [destination.address.country_code],
    (
        SELECT
            [Order Idwaybill] AS shipment_id,
            [Order Idwaybill] AS fulfillment_id,
            [Order Idwaybill] AS tracking_number
            'SEKW' AS carrier_scac
        FOR JSON PATH
    ) AS shipments
FROM vw__orders
WHERE [Order Number] = '12394334';

    Note however, that this won’t put multiple rows into the object unless you use a FROM. There may be some other method using GROUP BY and STRING_AGG, but your schema design is not clear, and appears to be denormalized.

    Login or Signup to reply.
  2. 0

    You can use ‘FOR JSON AUTO’ after your select statement

    SELECT 
[Order Number] AS [customer_order.order_number], [Order Number] AS [fulfillments.fulfillment_id]
,[Last Name] AS [destination.contact.person_name], [Home Phone] AS [destination.contact.phone_number], email AS [destination.contact.email_address]
,[Address] AS [destination.address.street_line1], [city] AS [destination.address.city], zip AS [destination.address.postal_code]
,[State] AS [destination.address.state_or_province_code], 'US' AS [destination.address.country_code]
,[Order Idwaybill] AS [shipments.shipment_id], [Order Idwaybill] AS [shipments.fulfillment_id], [Order Idwaybill] AS [shipments.tracking_number]
,'SEKW' AS [shipments.carrier_scac] 
FROM vw__orders FOR JSON AUTO 
WHERE [Order Number] = '12394334'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search