How to create JSON from an EAV table in SQL Server

Question

If you have a simple table like:

Id Name Age  
1  Saeed 32  
2  John  28  
3  David 34

Then you can create a JSON using For XML in SQL Server just like:

select '{ name : "' + Name + '", age : ' + age + ' }'
from People
where Id = 1
for xml path('')

This is easy, because columns are known beforehand. However, I'm stuck at creating JSON from an EAV table. For example, if the table is:

Id EntityId Key    Value
1  1        Name   Saeed
2  1        Age    32
3  1        Gender Male
4  1        Key1   Value1
5  1        Key2   Value2

How can I create this JSON?

{ Name: "Saeed", Age: 32, Gender: "Male", Key1: "Value1", Key2: "Value2" }

From this query:

select *
from PeopleEav
where EntityId = 1

Please note that the number of keys is variable (it's an EAV table).

display formatting does not belong to back end. Do it in the client. — Remus Rusanu, Sep 04 '13 at 04:58
Thanks for that precious info dear @RemusRusanu. Sometimes academic rules are not in action ;) — Saeed Neamati, Sep 04 '13 at 05:00
A couple people have tacked this with stored procedures, like [this](http://firozansari.com/2008/11/21/generating-json-from-tsql-query/) and [this](http://stackoverflow.com/a/15442030/864696). — Ross Presser, Sep 04 '13 at 05:05
Sometimes academic rules are not in action indeed. I just wonder why are they out of action in this case? — Lajos Arpad, Sep 04 '13 at 05:23
You mention `FOR XML` but you don't actually use it in the query. What's that about? — Damien_The_Unbeliever, Sep 04 '13 at 06:07

Devart · Accepted Answer · 2013-09-04T05:08:11.833

Try this one -

DECLARE @PeopleEav TABLE
(
      Id INT IDENTITY(1,1)
    , EntityId INT
    , [Key] VARCHAR(30)
    , Value VARCHAR(100)
)

INSERT INTO @PeopleEav (EntityId, [Key], Value)
VALUES
    (1, 'Name',   'Saeed'),
    (1, 'Age',    '32'),
    (1, 'Gender', 'Male'),
    (1, 'Key1',   'Value1'),
    (1, 'Key2',   'Value2')

SELECT 
      t.EntityId
    , JSON = STUFF((
        SELECT ', ' + [Key] + ': "' + Value + '"'
        FROM @PeopleEav t2
        WHERE t2.EntityId = t2.EntityId
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '{ ') + ' }'                  
FROM (
    SELECT DISTINCT EntityId
    FROM @PeopleEav
) t
--WHERE EntityId = 1

Output -

EntityId    JSON
----------- --------------------------------------------------------------------------------------------
1           { Name: "Saeed", Age: "32", Gender: "Male", Key1: "Value1", Key2: "Value2" }

score 0 · Answer 2 · answered Sep 29 '15 at 08:05

0

If you have Sql Server 2016 you can use FOR JSON. You can also use existing CLR libraries such as JsonSelect or Json4Sql.

answered Sep 29 '15 at 08:05

Jovan MSFT

13,232
4
40
55

A working example with FOR JSON for provided input would be good. – Oleksandr Feb 15 '19 at 14:16

How to create JSON from an EAV table in SQL Server

2 Answers2