Converting JSON with SQL - object and string for same property-CodePudding

I have a challenge converting a JSON array into a different output format using SQL Server...

Input:

[
    {
        "label": "City",
        "values": [
            "Test City"
        ]
    },
    {
        "label": "imgTest",
        "values": [
            {
                "identifier": "56696553-48F4-4BC5-BB43-FF4F71743EE9",
                "filename": "file1.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            },
            {
                "identifier": "2D9106D7-71A1-440E-8255-679E8905B32E",
                "filename": "file2.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            }
        ]
    }
]

Desired output:

[
    {
        "label": "City",
        "answer": "Test City"
    },
    {
        "label": "imgTest",
        "answer": {
            "filename": "file1.jpg",
            "contentType": "image/jpg",
            "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
        }
    },
    {
        "label": "imgTest",
        "answer": {
            "filename": "file2.jpg",
            "contentType": "image/jpg",
            "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
        }
    }
]

The challenge I find lies in the combination of a string and an object as the 'answer'-property in the output.

Note this is but a small subset of very much nested JSON input. Thanks. Temporary tables etc are perfectly possible, as this would most likely happen via a stored procedure

Reason I am referring to SQL is because that is the technology I am most familiar with, and that has been used for these purposes successfully in the past.

My initial approach before the images came into play was to build a temp table with different layers of 'label-values'-pairs, and then turn this table into a json with a series of nested 'for json path' statements.

However, when I do this with the image-data, it assumes the content is string so it escapes the characters which causes an issue when sending the output to the next application...

CodePudding user response：

try this. It was tested in Visual Studio

    var jArray = JArray.Parse(json);

    foreach (var jObject in jArray)
    {
        var data = new Data { Label = jObject["label"].ToString() };    
        var result = new List<Data>();

        foreach (var jArr in (JArray)jObject["values"])
        {
            if (jArr.GetType().Name.ToString() == "JValue") data.ShortAnswer = ((JValue)jArr).ToString();
            else
            {
            if (data.Answer == null) data.Answer = new List<Answer>();
            data.Answer.Add(jArr.ToObject<Answer>());
            }
        }
        result.Add(data);
   }

result

[
  {
    "label": "City",
    "answer": null,
    "shortAnswer": "Test City"
  },
  {
    "label": "imgTest",
    "answer": [
      {
        "filename": "file1.jpg",
        "contentType": "image/jpg",
        "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
      },
      {
        "filename": "file2.jpg",
        "contentType": "image/jpg",
        "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
      }
    ],
    "shortAnswer": null
  }
]

classes

public partial class Data
{
    [JsonProperty("label")]
    public string Label { get; set; }

    [JsonProperty("answer")]
    public List<Answer> Answer { get; set; }

    [JsonProperty("shortAnswer")]
    public string ShortAnswer { get; set; }
}

public partial class Answer
{
    [JsonProperty("filename")]
    public string Filename { get; set; }

    [JsonProperty("contentType")]
    public string ContentType { get; set; }

    [JsonProperty("bytes")]
    public string Bytes { get; set; }
}

CodePudding user response：

By default, the FOR JSON clause escapes the special characters in the JSON output with \ and usually you can avoid this by using JSON_QUERY() (as is explained in the documentation ...JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value).

As you already know, the problem here is that the $."values" part of the JSON contains a string and an object. A possible solution to this problem is to generate a table with two columns with the same name and apply the FOR JSON AUTO clause. The output of the FOR JSON AUTO is determined by a combination of the order of columns in the SELECT clause and the tables that are referenced in the SELECT clause, so I'm not sure if this output is "by design" or just an useful side effect. Of course, if you include the INCLUDE_NULL_VALUES option, the result will include the values from both columns.

JSON:

DECLARE @json nvarchar(max) = N'
[
    {
        "label": "City",
        "values": [
            "Test City"
        ]
    },
    {
        "label": "imgTest",
        "values": [
            {
                "identifier": "56696553-48F4-4BC5-BB43-FF4F71743EE9",
                "filename": "file1.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            },
            {
                "identifier": "2D9106D7-71A1-440E-8255-679E8905B32E",
                "filename": "file2.jpg",
                "contentType": "image/jpg",
                "bytes": "iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
            }
        ]
    }
]'

T-SQL:

SELECT 
   [label], 
   CASE WHEN ISJSON([value]) = 0 THEN [value] END AS [answer],
   JSON_QUERY(CASE WHEN ISJSON([value]) = 1 THEN [value] END) AS [answer]
FROM (
   SELECT j1.[label], j2.[value]
   FROM OPENJSON(@json) WITH (
      [label] nvarchar(100) '$.label',
      [values] nvarchar(max) '$.values' AS JSON
   ) j1
   OUTER APPLY OPENJSON(j1.[values]) j2
) t   
FOR JSON AUTO, INCLUDE_NULL_VALUES

Result:

[
   {
      "label":"City",
      "answer":"Test City"
   },
   {
      "label":"imgTest",
      "answer":{
         "identifier":"56696553-48F4-4BC5-BB43-FF4F71743EE9",
         "filename":"file1.jpg",
         "contentType":"image/jpg",
         "bytes":"iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
      }
   },
   {
      "label":"imgTest",
      "answer":{
         "identifier":"2D9106D7-71A1-440E-8255-679E8905B32E",
         "filename":"file2.jpg",
         "contentType":"image/jpg",
         "bytes":"iVBORw0KGgoAAAANSUhEUgAAAAgAAAAIAQMAAAD wSzIAAAABlBMVEX/// /v7 jQ3Y5AAAADklEQVQI12P4AIX8EAgALgAD/aNpbtEAAAAASUVORK5CYII"
      }
   }
]