How to return raw sql queries as json objects?(Django)

January 3, 2022

I am using the following function to display the sql join query as json:

def toggle_query(request,id):
    obj = DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.raw('''SELECT *
    FROM dibbs_spider_solicitation
    JOIN dibbs_spider_dibbs_matched_product_fields_duplicate
    ON dibbs_spider_solicitation.nsn = {nsn};'''.format(nsn=id))

    context = serializers.serialize('json',obj)

    return JsonResponse(context,safe=False)

This displays data as :

"[{\"model\": \"dibbs_spider.dibbsspiderdibbsmatchedproductfieldsduplicate\", \"pk\": 39, \"fields\": {\"nsn\": \"1650011162908\", \"nsn2\": \"6550015746831\", \"cage\": \"34807\", \"part_number\": \"6903A-J\", \"company_name\": null, \"supplier\": \"GraingerOM\", \"cost\": \"18.16\", \"list_price\": \"19.12\", \"gsa_price\": null, \"hash\": \"665\", \"nomenclature\": \"HOUSING, CONTROL MOD\", \"technical_documents\": \"Tech Docs\", \"solicitation\": \"SPE2DS22T1575\", \"status\": \"Awarded  \", \"purchase_request\": \"0091648844\\nQty: 5\", \"issued\": \"2021-12-09\", \"return_by\": \"2021-12-14\", \"file\": \"https://dibbs2.bsm.dla.mil/Downloads/RFQ/5/SPE2DS22T1575.PDF\", \"vendor_part_number\": \"269P71\", \"manufacturer_name\": \"LAMOTTE\", \"product_name\": \"Reagent Tablet Type 0 to 4 ppm Range\", \"unit\": \"EA\"}}, {\"model\":

How to remove the \ symbol which is not the part of the data here ?

>Solution :

You can’t remove it, because when you serialise the object to JSON, that \ is required in order to escape the " and `. You’re probably best to turn the response into a dict() and return that instead.