Remove Double quota from Nested JSON Dictionary

Question

I'm making api from mysql to json format. here is my code:

@blueprint.route('/api/products', methods=['GET', 'POST'])
def products():
    sql = text('SELECT products.id, products.product_name, products.image_name, products.product_type, '
               'units.unit_name, categories.category_name, brands.brand_name, '
               'JSON_ARRAY( JSON_OBJECT("price" ,GROUP_CONCAT(products_variation.price))) AS price, '
               'FROM `products` '
               'INNER JOIN products_variation ON products_variation.product_id = products.id '
               'INNER JOIN units ON units.id = products.unit_id '
               'INNER JOIN categories ON categories.id = products.category_id '
               'INNER JOIN brands ON brands.id = products.brand_id '
               'GROUP BY products.product_name')
    product_all = db.engine.execute(sql)
    product_schema = ProductsSchema(many=True)
    data = product_schema.dump(product_all)
    return data

http://127.0.0.1:5000/api/products

Output :

[{
    "brand_name": "Naviforce",
    "category_name": "Watch",
    "id": 1,
    "option_id": 1,
    "option_name": "Color",
    "price": "[{\"sku\": \"9199G\", \"price\": 29.99},{\"sku\": \"9199S\", \"price\": 27.99},{\"sku\": \"9199B\", \"price\": 28.99}]",
    "product_name": "NAVIFORCE Men Luxury Watches NF9199S",
    "product_type": "variable",
    "unit_name": "Pieces ",
  },
  {
    "brand_name": "Texas Instruments",
    "category_name": "Electronics",
    "id": 3,
    "option_id": null,
    "option_name": null,
    "price": "[{\"sku\": \"TI-84 Plus\", \"price\": 9500.00}]",
    "product_name": "Texas Instruments TI-84 Plus Graphics Calculator",
    "product_type": "single",
    "unit_name": "Pieces ",
  }]

I want to try doing like this :

"price": [{"sku": "9199G", "price": 29.99},{"sku": "9199S", "price": 27.99},{"sku": "9199B", "price": 28.99}],

where remove double quota.

Or how to extract value with double quota using python Flask:

@blueprint.route('/')
def index():
    url = request.base_url + "api/products"
    response = requests.get(url)
    products = response.json()
    print(products[0]['price'])
    return render_template('theme/test.html', products=products)

Answer 1

Your output data (which is assumed to be a string) does not constitute valid JSON for 2 reasons:

1. There is a redundant comma here: "unit_name": "Pieces ",

2. Double quote escaping is flawed.

Here's what the output data needs to look like before you can process it as JSON and convert to a Python dictionary. You can use literal_eval() from the ast module to handle the 'price' value as follows:

from json import dumps as DUMPS, loads as LOADS
from ast import literal_eval as EVAL

original = """[{
    "brand_name": "Naviforce",
    "category_name": "Watch",
    "id": 1,
    "option_id": 1,
    "option_name": "Color",
    "price": "[{\\"sku\\": \\"9199G\\", \\"price\\": 29.99},{\\"sku\\": \\"9199S\\", \\"price\\": 27.99},{\\"sku\\": \\"9199B\\", \\"price\\": 28.99}]",
    "product_name": "NAVIFORCE Men Luxury Watches NF9199S",
    "product_type": "variable",
    "unit_name": "Pieces "
  },
  {
    "brand_name": "Texas Instruments",
    "category_name": "Electronics",
    "id": 3,
    "option_id": null,
    "option_name": null,
    "price": "[{\\"sku\\": \\"TI-84 Plus\\", \\"price\\": 9500.00}]",
    "product_name": "Texas Instruments TI-84 Plus Graphics Calculator",
    "product_type": "single",
    "unit_name": "Pieces "
  }]"""

result = []

for d in LOADS(original):
    if 'price' in d:
        d['price'] = EVAL(d['price'])
    result.append(d)

print(DUMPS(result, indent=4))

Output:

[
    {
        "brand_name": "Naviforce",
        "category_name": "Watch",
        "id": 1,
        "option_id": 1,
        "option_name": "Color",
        "price": [
            {
                "sku": "9199G",
                "price": 29.99
            },
            {
                "sku": "9199S",
                "price": 27.99
            },
            {
                "sku": "9199B",
                "price": 28.99
            }
        ],
        "product_name": "NAVIFORCE Men Luxury Watches NF9199S",
        "product_type": "variable",
        "unit_name": "Pieces "
    },
    {
        "brand_name": "Texas Instruments",
        "category_name": "Electronics",
        "id": 3,
        "option_id": null,
        "option_name": null,
        "price": [
            {
                "sku": "TI-84 Plus",
                "price": 9500.0
            }
        ],
        "product_name": "Texas Instruments TI-84 Plus Graphics Calculator",
        "product_type": "single",
        "unit_name": "Pieces "
    }
]