0

I want to write a stored procedure that returns nested json. I'm currently trying to write the query to do this and I'm a little confused about the format I need to write it in.

I want json that looks something like this:

    {
      [{
        item: "string",
        item2: 0,
        groups: [{
          item: "string",
          item2: "string",
          sub_groups[{
            item: "string",
            item2: "string"
          }, {
            item: "string",
            item2: "string"
          }]
        }, {
          item: "string",
          item2: "string",
          sub_groups[{
            item: "string",
            item2: "string"
          }, {
            item: "string",
            item2: "string"
          }]
        }]
      }, {
        item: "string",
        item2: 0,
        groups: [{
          item: "string",
          item2: "string",
          sub_groups[{
            item: "string",
            item2: "string"
          }, {
            item: "string",
            item2: "string"
          }]
        }, {
          item: "string",
          item2: "string",
          sub_groups[{
            item: "string",
            item2: "string"
          }, {
            item: "string",
            item2: "string"
          }]
        }]
      }]
    }

An array of objects that contain values, and an array of objects that contain values, and an array of objects that contain values.

Or to put it differently, I have a table that has a one to many relationship with another table that has a one to many relationship to a third table and I want to print out json that represents this.

I have tried to write a query for this but I'm new to SQL and I'm having difficulty withing a "nested query." I'm not sure that you could even do that with normal SQL because you would just need to print all this information from the outer nests over again the same number of times you have results for the inner nests.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cOborski
  • 134
  • 1
  • 15
  • 2
    You can use path syntax (see [this example](https://learn.microsoft.com/sql/relational-databases/json/format-nested-json-output-with-path-mode-sql-server?view=sql-server-2017#example---multiple-tables) in the docs) and [`JSON AUTO` natively supports `JOIN`s](https://learn.microsoft.com/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017#2b---example-with-join-and-null). You can use subqueries to massage your data into proper form if the tables aren't exactly right, which is easier than writing out all the nesting explicitly. – Jeroen Mostert Jul 22 '19 at 12:30
  • @JeroenMostert You're right, I'm overcomplicating this. If I just use inner joins like normal with the path syntax I should be fine. I had that webpage open and was staring at it blankly for no reason. I'm going to go get more coffee and knock this out... Thanks! – cOborski Jul 22 '19 at 12:41
  • @JeroenMostert If you post that as an answer I'll mark it correct. – cOborski Jul 22 '19 at 12:42
  • @ZoharPeled It is. I acknowledge that this has not been my highest quality question posted on stackoverflow. – cOborski Jul 22 '19 at 13:45
  • I'm not talking about the quality of the question, if it's a dupe then lets close it as one. – Zohar Peled Jul 22 '19 at 13:47
  • @ZoharPeled I flagged it, is it possible for me to close it as a duplicate? I'm not sure how to proceed. Also, the answer given by JeroenMostert in the comments is different than the answer in the other question and was helpful in my situation. – cOborski Jul 22 '19 at 13:51
  • 1
    It's not yet possible for you but it is possible for anyone with a gold badge on any of the tags in your question (A.K.A dupe hammer), or for five different people with over 250 reputation points each. Since you agree it's a duplicate,Community user have closed it as a dupe. – Zohar Peled Jul 22 '19 at 14:06

0 Answers0