0

We are receiving this JSON document and importing into SQL Server 2016 using Kingswaysoft JSON Source component

{
    "Program": "USRMI",
    "Transaction": "GetUserInfo",
    "Metadata": {
        "Field": [
            {
                "@name": "USERID",
                "@type": "A",
                "@length": 10,
                "@description": "User"
            },
            {
                "@name": "COMPNO",
                "@type": "N",
                "@length": 3,
                "@description": "Company"
            },
        ]
    },
    "MIRecord": [
        {
            "NameValue": [
                {
                    "Name": "USERID",
                    "Value": "JOEBLO88"
                },
                {
                    "Name": "COMPNO",
                    "Value": "999"
                },

            ],
            "RowIndex": 0
        }
        {
            "NameValue": [
                {
                    "Name": "USERID",
                    "Value": "JOEBLO55"
                },
                {
                    "Name": "COMPNO",
                    "Value": "998"
                },
            ],
            "RowIndex": 1
        }       
    ]
}

and have a column in a table as follows:

{"Name": "USERID","Value": "JOBLO88"}
{"Name": "COMPNO","Value": "988"}
{"Name": "USERID","Value": "JOBLO55"}
{"Name": "COMPNO","Value": "955"}

I'm struggling to find any resource on syntax that can be used to convert this into a column structured table as per below

USERID  COMPNO
======  ======
JOBLO88  988
JOBLO55  955
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
dazcuk
  • 1
  • 1
  • That's not valid JSON. Commas after the last element in an array are not allowed. A common mistake while downsizing valid JSON, or possibly a broken generator. You can use jsonlint.com to check such things. SQL Server can't parse or fix invalid JSON on its own, so that does matter. – Jeroen Mostert Sep 13 '18 at 12:53
  • I'm also not clear on what you're asking: do you need to process the first JSON into the second JSON *and* a table, or do you only have the second JSON and do you need a table out of that, or some exciting combination of the two? – Jeroen Mostert Sep 13 '18 at 12:54
  • @JeroenMostert yes your correct i've doctored the json slightly to show an example and added that in by mistake – dazcuk Sep 13 '18 at 13:09
  • @JeroenMostert currently we import the full json file which can result in upto 4 separate tables (Root, NameValue, Field, MIRecord arrays/objects tables) so we could do something exciting with any of them or just use the 2nd json table (NameValue) to output a new table in the column format we require – dazcuk Sep 13 '18 at 13:15
  • Do you have the tables in advance and do you want to put the JSON data in them, or do you want to generate the tables based on the JSON? The latter is much harder than the former, because T-SQL is statically typed (mostly) so you need some hairy code to figure out what columns the table should have (this is best done outside SQL Server). Parsing JSON is done with `OPENJSON` and `JSON_QUERY`, but these can't produce dynamic tables; you do need to specify the structure in advance. – Jeroen Mostert Sep 13 '18 at 13:31
  • @JeroenMostert yes we'll create all the tables in advance like you say dynamically would be a nightmare – dazcuk Sep 13 '18 at 13:48

0 Answers0