Creating aggregated JSON objects

Question

My database looks like this:

Raw Table Data :

The Result returned :

from the query below :

SELECT ROW_NUMBER() OVER ( ORDER BY stokkodu ) AS RowNum,
       stokkodu,
       adi,
       beden,
       renk,
       kalan,
       afiyat
  FROM (SELECT stokkodu, adi, afiyat, renk, SUM(kalan) AS kalan, beden
          FROM [viabase].[dbo].[Q_KALANS]
         GROUP BY stokkodu, adi, afiyat, renk, beden) AS products

Now I need to get the sizes of the products grouped under separate color nodes to fetch from the DB to present to the client site as in the JSON Format like this :

How can I do that? Is it possible?

MERGE has a specific meaning in SQL; ity an operator that can be used to INSERT, UPDATE and DELETE against an object in a single statement. What you seem to want here is some form of malformed JSON. — Thom A
– Thom A ♦, Commented Jul 9, 2022 at 16:04
I don't know the terms in SQL this is why I am asking so all I want is to get data like I mentioned in post. — whitehead421
– whitehead421, Commented Jul 9, 2022 at 16:10
Hi whitehead421 . Unfortunately, the summed up amounts are inconsistent with the row data. — Barbaros Özhan
– Barbaros Özhan, Commented Jul 9, 2022 at 19:11
Please don't use images for data, code or errors. Use formatted text. — Dale K
– Dale K, Commented Jul 9, 2022 at 23:42
Sample data and expected results as text are a must. We cannot copy and paste any of this. — Charlieface
– Charlieface, Commented Jul 10, 2022 at 2:48

Charlieface · Accepted Answer · 2022-07-10 03:05:17Z

3

Unfortunately, SQL Server does not have the JSON_AGG function, which would have made this easier. Instead we need to hack it with a combination of STRING_AGG to aggregate, STRING_ESCAPE to correctly escape the values, and JSON_QUERY to prevent double-escaping.

You also need to get the base adi value. I've tried to do that with REPLACE and TRIM but cannot test due to lack of proper sample data.

WITH L1 AS (
  SELECT
    adi = TRIM(REPLACE(REPLACE(qk.adi, qk.renk, ''), qk.beden, '')),
    qk.renk,
    qk.beden,
    SUM(qk.kalan) AS kalan
  FROM dbo.Q_KALANS qk
  GROUP BY
    qk.adi,
    qk.renk,
    qk.beden
),
L2 AS (
  SELECT
    qk.adi,
    qk.renk,
    json = '{' + STRING_AGG(CONCAT('"', STRING_ESCAPE(qk.beden, 'json'), '":', SUM(kalan)), ',') + '}'
  FROM L1 qk
  GROUP BY
    qk.adi,
    qk.renk
)
SELECT
    qk.adi,
    STOCK = JSON_QUERY('{' + STRING_AGG(CONCAT('"', STRING_ESCAPE(qk.renk, 'json'), '":', qk.json), ',') + '}')
FROM L2 qk
GROUP BY
    qk.adi
FOR JSON PATH;

db<>fiddle

answered Jul 10, 2022 at 3:05

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

1 Comment

whitehead421 Over a year ago

Thank you so much, this worked me with few edits.

Collectives™ on Stack Overflow

Creating aggregated JSON objects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related