JSON from SQL Server: Reduce lists of single dicts to list of values

Question

SQL Server 2016 introduced some JSON features, but they're far from robust. As far as I can tell, there's no way yet to output a simple array; everything must be in "key": "value" format. I don't want to do what some answers propose and write a partial JSON encoder within SQL.

Therefore, I'm stripping out certain keys in the Python side of my program.

SQL statement (I'd tag this with PowerCampus, the ERP product, if the tag existed):

SELECT SECTIONS.EVENT_ID
    ,SECTIONS.EVENT_LONG_NAME
    ,SECTIONS.ACADEMIC_YEAR
    ,SECTIONS.ACADEMIC_TERM
    ,SECTIONS.ACADEMIC_SESSION
    ,SECTIONS.EVENT_SUB_TYPE
    ,SECTIONS.SECTION
    ,TRANSCRIPTDETAIL.PEOPLE_CODE_ID
FROM [SECTIONS]
INNER JOIN [TRANSCRIPTDETAIL]
    ON TRANSCRIPTDETAIL.EVENT_ID = SECTIONS.EVENT_ID
        AND TRANSCRIPTDETAIL.ACADEMIC_YEAR = SECTIONS.ACADEMIC_YEAR
        AND TRANSCRIPTDETAIL.ACADEMIC_TERM = SECTIONS.ACADEMIC_TERM
        AND TRANSCRIPTDETAIL.ACADEMIC_SESSION = SECTIONS.ACADEMIC_SESSION
        AND TRANSCRIPTDETAIL.EVENT_SUB_TYPE = SECTIONS.EVENT_SUB_TYPE
        AND TRANSCRIPTDETAIL.SECTION = SECTIONS.SECTION
        AND TRANSCRIPTDETAIL.ADD_DROP_WAIT = 'A'
WHERE SECTIONS.ACADEMIC_YEAR = '2019'
    AND SECTIONS.ACADEMIC_TERM = 'SPRING'
FOR JSON AUTO

JSON output:

[
    {
        "SectionId": "ENG101",
        "LongName": "English 102",
        "AcademicYear": "2019",
        "AcademicTerm": "SPRING",
        "AcademicSession": "01",
        "SubType": "LEC",
        "Section": "ABC",
        "Students": [
            {
                "PeopleCodeId": "P000111602"
            },
            {
                "PeopleCodeId": "P000109552"
            },
            {
                "PeopleCodeId": "P000110652"
            },
            {
                "PeopleCodeId": "P000111872"
            },
            {
                "PeopleCodeId": "P000111772"
            },
            {
                "PeopleCodeId": "P000111802"
            },
            {
                "PeopleCodeId": "P000111792"
            },
            {
                "PeopleCodeId": "P000111722"
            },
            {
                "PeopleCodeId": "P000111802"
            },
            {
                "PeopleCodeId": "P000111442"
            }
        ]
    },
    {
        "EVENT_ID": "ENG101",
        "EVENT_LONG_NAME": "English 102",
        "ACADEMIC_YEAR": "2019",
        "ACADEMIC_TERM": "SPRING",
        "ACADEMIC_SESSION": "01",
        "EVENT_SUB_TYPE": "LEC",
        "SECTION": "ABC",
        "TRANSCRIPTDETAIL": [
            {
                "PEOPLE_CODE_ID": "P000112582"
            },
            {
                "PEOPLE_CODE_ID": "P000113022"
            },
            {
                "PEOPLE_CODE_ID": "P000113062"
            },
            {
                "PEOPLE_CODE_ID": "P000112152"
            },
            {
                "PEOPLE_CODE_ID": "P000112212"
            },
            {
                "PEOPLE_CODE_ID": "P000112812"
            },
            {
                "PEOPLE_CODE_ID": "P000112662"
            },
            {
                "PEOPLE_CODE_ID": "P000112072"
            },
            {
                "PEOPLE_CODE_ID": "P000112222"
            },
            {
                "PEOPLE_CODE_ID": "P000112442"
            }
        ]
    }
]

My Python function correctly collapses the PEOPLE_CODE_ID's into a simple array:

[
    {
        "SectionId": "ENG101",
        "LongName": "English 102",
        "AcademicYear": "2019",
        "AcademicTerm": "SPRING",
        "AcademicSession": "01",
        "SubType": "LEC",
        "Section": "ABC",
        "Students": [
            "P000111602",
            "P000109552",
            "P000110652",
            "P000111872",
            "P000111772",
            "P000111802",
            "P000111792",
            "P000111722",
            "P000111802",
            "P000111442"
        ]
    },
    {
        "EVENT_ID": "ENG101",
        "EVENT_LONG_NAME": "English 102",
        "ACADEMIC_YEAR": "2019",
        "ACADEMIC_TERM": "SPRING",
        "ACADEMIC_SESSION": "01",
        "EVENT_SUB_TYPE": "LEC",
        "SECTION": "ABC",
        "TRANSCRIPTDETAIL": [
            "P000112582",
            "P000113022",
            "P000113062",
            "P000112152",
            "P000112212",
            "P000112812",
            "P000112662",
            "P000112072",
            "P000112222",
            "P000112442"
        ]
    }
]

Here's the function:

def clean_sql_json(x):
    """Cleans up JSON produced by SQL Server by reducing this pattern:
        [{"Key": [{"Key": "Value"}]}]
    to this:
        [{'Key': ['Value']}]

    Also removes duplicates (and ordering) from the reduced list.
    """

    data = json.loads(x)

    for k in data:
        for kk, vv in k.items():
            if type(vv) == list and type(vv[0] == dict) and len(vv[0]) == 1:
                newlist = [kkk[list(vv[0].keys())[0]] for kkk in vv]
                data[data.index(k)][kk] = list(set(newlist))

    return data

I'm a little concerned about the Python. Is it safe to modify and return data on the fly like this?

Answer 1

• type(x) == y is unidiomatic, and prone to errors. Use isinstance(x, y) instead.
• Your variable names aren't great. k, kk, kkk, vv.
• You can simplify changing the data. data[data.index(k)] == k.
• You can simplify kkk[list(vv[0].keys())[0]] to list(kkk.values())[0].
• You can simplify set([...]) with a set comprehension {...}.
• Your function looks like a hack. I wouldn't want this to reach production.

def clean_sql_json(x):
    datas = json.loads(x)

    for data in datas:
        for key, value in data.items():
            if (isinstance(value, list)
                and isinstance(value[0], dict)
                and len(value[0]) == 1
            ):
                data[key] = list({
                    list(item.values())[0]
                    for item in value
                })

    return datas

The way I'd further improve this is:

1. Create a wrapper that eased walking the JSON tree.
2. Walk the tree. When a list is found check if all it's children are dictionaries with one item.
3. Reduce the dictionaries, like you are now.

This would have the benefit that it can work on any and all values you give it. However it would be overkill if this is the only time you are doing this.

I'm a little concerned about the Python. Is it safe to modify and return data on the fly like this?

Yes it's safe to do this, as you create the data in that function. datas = json.loads(x).

It can be safe to implement it in a function where this isn't the case, but you shouldn't return the data. This shows that the output is via side-effects. If you return and mutate then that will cause people to think there are no side-effects and it would be unsafe.

It also makes the code simpler than otherwise.