This works in SQL Server:
declare @jstring varchar(max)='{
"FieldInfo": {
"Template": "Babies",
"Groups": [{
"Group": "Required",
"SortOrder": 1,
"BackgroundColor": "FCD5B4",
"Fields": [{
"fieldName": "feed_product_type",
"SortOrder": 1,
"labelName": "Product Type",
"defaultValue": "",
"columnIndex": 0
}, {
"fieldName": "item_sku",
"SortOrder": 2,
"labelName": "Seller SKU",
"defaultValue": "",
"columnIndex": 1
}, {
"fieldName": "brand_name",
"SortOrder": 3,
"labelName": "Brand",
"defaultValue": "",
"columnIndex": 2
}, {
"fieldName": "item_name",
"SortOrder": 4,
"labelName": "Title",
"defaultValue": "",
"columnIndex": 3
}, {
"fieldName": "external_product_id",
"SortOrder": 5,
"labelName": "Product ID",
"defaultValue": "",
"columnIndex": 4
}, {
"fieldName": "external_product_id_type",
"SortOrder": 6,
"labelName": "Product ID Type",
"defaultValue": "",
"columnIndex": 5
}, {
"fieldName": "item_type",
"SortOrder": 7,
"labelName": "Item Type Keyword",
"defaultValue": "",
"columnIndex": 6
}, {
"fieldName": "model",
"SortOrder": 8,
"labelName": "Model Number",
"defaultValue": "",
"columnIndex": 7
}, {
"fieldName": "manufacturer",
"SortOrder": 9,
"labelName": "Manufacturer",
"defaultValue": "",
"columnIndex": 8
}, {
"fieldName": "part_number",
"SortOrder": 10,
"labelName": "Part Number",
"defaultValue": "",
"columnIndex": 9
}, {
"fieldName": "mfg_minimum",
"SortOrder": 11,
"labelName": "Minimum Manufacturer Age Recommended",
"defaultValue": "",
"columnIndex": 10
}, {
"fieldName": "standard_price",
"SortOrder": 12,
"labelName": "Standard Price",
"defaultValue": "",
"columnIndex": 11
}, {
"fieldName": "quantity",
"SortOrder": 13,
"labelName": "Quantity",
"defaultValue": "",
"columnIndex": 12
}, {
"fieldName": "main_image_url",
"SortOrder": 14,
"labelName": "Main Image URL",
"defaultValue": "",
"columnIndex": 13
}]
}, {
"Group": "Images",
"SortOrder": 2,
"BackgroundColor": "FFFF00",
"Fields": [{
"fieldName": "other_image_url1",
"SortOrder": 15,
"labelName": "Other Image URL1",
"defaultValue": "",
"columnIndex": 14
}, {
"fieldName": "other_image_url2",
"SortOrder": 16,
"labelName": "Other Image URL2",
"defaultValue": "",
"columnIndex": 15
}, {
"fieldName": "other_image_url3",
"SortOrder": 17,
"labelName": "Other Image URL3",
"defaultValue": "",
"columnIndex": 16
}, {
"fieldName": "other_image_url4",
"SortOrder": 18,
"labelName": "Other Image URL4",
"defaultValue": "",
"columnIndex": 17
}, {
"fieldName": "other_image_url5",
"SortOrder": 19,
"labelName": "Other Image URL5",
"defaultValue": "",
"columnIndex": 18
}, {
"fieldName": "other_image_url6",
"SortOrder": 20,
"labelName": "Other Image URL6",
"defaultValue": "",
"columnIndex": 19
}, {
"fieldName": "other_image_url7",
"SortOrder": 21,
"labelName": "Other Image URL7",
"defaultValue": "",
"columnIndex": 20
}, {
"fieldName": "other_image_url8",
"SortOrder": 22,
"labelName": "Other Image URL8",
"defaultValue": "",
"columnIndex": 21
}, {
"fieldName": "swatch_image_url",
"SortOrder": 23,
"labelName": "Swatch Image URL",
"defaultValue": "",
"columnIndex": 22
}]
}, {
"Group": "Variation",
"SortOrder": 3,
"BackgroundColor": "FF8080",
"Fields": [{
"fieldName": "parent_child",
"SortOrder": 24,
"labelName": "Parentage",
"defaultValue": "",
"columnIndex": 23
}, {
"fieldName": "relationship_type",
"SortOrder": 25,
"labelName": "Relationship Type",
"defaultValue": "",
"columnIndex": 24
}, {
"fieldName": "parent_sku",
"SortOrder": 26,
"labelName": "Parent SKU",
"defaultValue": "",
"columnIndex": 25
}, {
"fieldName": "variation_theme",
"SortOrder": 27,
"labelName": "Variation Theme",
"defaultValue": "",
"columnIndex": 26
}]
}, {
"Group": "Basic",
"SortOrder": 4,
"BackgroundColor": "F8A45E",
"Fields": [{
"fieldName": "update_delete",
"SortOrder": 28,
"labelName": "Update Delete",
"defaultValue": "",
"columnIndex": 27
}, {
"fieldName": "product_description",
"SortOrder": 29,
"labelName": "Description",
"defaultValue": "",
"columnIndex": 28
}, {
"fieldName": "care_instructions",
"SortOrder": 30,
"labelName": "Care Instructions",
"defaultValue": "",
"columnIndex": 29
}, {
"fieldName": "target_gender",
"SortOrder": 31,
"labelName": "Target Gender",
"defaultValue": "",
"columnIndex": 30
}, {
"fieldName": "edition",
"SortOrder": 32,
"labelName": "Edition",
"defaultValue": "",
"columnIndex": 31
}]
}]
}
}'
--drop table #AmazonTemplateInfo
;with FieldInfo as (
select Template,[Group],GroupSort,BackgroundColor,FieldName,FieldSort,LabelName,DefaultValue,ColumnIndex from OPENJSON(@jstring,'$.FieldInfo')
With (
Template varchar(50)
,Groups nvarchar(max) as json
) as Template
cross apply openjson (Template.Groups)
with (
[Group] varchar(50)
,BackgroundColor varchar(6)
,GroupSort int '$.SortOrder'
,Fields nvarchar(max) as json
) Groups
cross apply openjson(Groups.Fields)
with (
fieldName varchar(50)
,FieldSort int '$.SortOrder'
,labelName varchar(50)
,defaultValue varchar(max)
,columnIndex int
) Fields
)
select * from FieldInfo
This generates a table in the form
| Template | Group | GroupSort | BackgroundColor | FieldName | FieldSort | LabelName | DefaultValue | ColumnIndex |
|---|---|---|---|---|---|---|---|---|
| Babies | Required | 1 | FCD5B4 | feed_product_type | 1 | Product Type | 0 |
I need to create the same functionality in MariaDB v10. I've tried using JSON_EXTRACT and the LATERAL statement for my cross applies, but nothing is working. JSON_EXTRACT seems to just create a JSON string from the selected columns.
Is this pointless in MariaDB? Is there any way to do this?
