How to write a query for a nested object that varies on a field of the parent?

Question

This may be a difficult question to ask correctly. The idea is that I have a thing that has a schema that could result in a json object like so:

{ id:1, x:1, y:1, type:"map" settings: {... those settings for type "map" ...}}

Each of the fields id, x, y, type are directly from a column in a table. What I want then is to take the string of type "map", and look up in the Map_Settings table those properties in the properties.

I could also have an instance:

{ id:1, x:1, y:1, type:"graph" settings: {... those settings for type "map" ...}}

Which has a different type. And so would reference the Graph_Settings table instead.

How can I write a query to do that?

Can you post the table schemas and any work you have done so far on the query? — nathan_jr, Jun 09 '14 at 22:05

score 0 · Answer 1 · answered Jun 10 '14 at 02:24

I'm not sure how you get SQL Server to produce JSON, but you could produce something similar in XML. Perhaps there is a way to convert XML to JSON..

I'm assuming your schema is something like this:

CREATE TABLE [Thing]
(
    [id] INT,
    [x] INT,
    [y] INT,
    [type] VARCHAR(10)
)

CREATE TABLE [Map_Settings]
(
    [Thing_id] INT,
    [MapSetting1] INT,
    [MapSetting2] INT
)

CREATE TABLE [Graph_Settings]
(
    [Thing_id] INT,
    [GraphSetting3] INT,
    [GraphSetting4] INT
)

Some data:

INSERT [Thing] VALUES (1, 1, 1, 'map'), (2, 1, 1, 'graph')
INSERT [Map_Settings] VALUES (1, 0, 0)
INSERT [Graph_Settings] VALUES (2, 0, 0)

And a query that produces XML:

SELECT
    thing.id AS [@id],
    thing.[x] AS [@x],
    thing.[y] AS [@y],
    thing.[type] AS [@type],
    CASE thing.[type]
        WHEN 'map' THEN
            (
                SELECT [MapSetting1] AS [@mapsetting1], [MapSetting2] AS [@mapsetting2]
                FROM [Map_Settings] ms
                WHERE ms.[Thing_id] = thing.[id]
                FOR XML PATH ('settings'), TYPE
            )
        WHEN 'graph' THEN
            (
                SELECT [GraphSetting3] AS [@graphsetting3], [GraphSetting4] AS [@graphsetting4]
                FROM [Graph_Settings] gs
                WHERE gs.[Thing_id] = thing.[id]
                FOR XML PATH ('settings'), TYPE
            )
    END
FROM [Thing] thing
FOR XML PATH ('Thing')

The output:

<Thing id="1" x="1" y="1" type="map">
  <settings mapsetting1="0" mapsetting2="0" />
</Thing>
<Thing id="2" x="1" y="1" type="graph">
  <settings graphsetting3="0" graphsetting4="0" />
</Thing>

Does this help?

How to write a query for a nested object that varies on a field of the parent?

1 Answers1