JSON to maintain array on predefined objects
ajit4sbi opened this issue · 10 comments
@wader , Opening discussion at right forum now.
Could you please provide a combined command to do both tasks at once
- I would like to only specify few objects to be an array like ".aitem, .oitem, .sitem" but not all the JSON.
- convert all numbers/floats/booleans without double quotes as not a string.
<root>
<aitem>
<name>abc</name>
<value>123</value>
</aitem>
<bitem>
<name>bbbb</name>
<value>2222</value>
</bitem>
<bitem>
<name>BB</name>
<value>22</value>
</bitem>
</root>
Expected Output , Only specified like .aitem, .oitem, .sitem must become array but not entire JSON & All numbers/floats/booleans are as non string without double quoted.
{
"root": {
"aitem": [
{
"name": "abc",
"value": 123
}
],
"bitem": [
{
"name": "bbbb",
"value": 2222
},
{
"name": "BB",
"value": true
}
]
}
}
Hey, to always produce arrays i think a schema or some new option would be needed (fq's xml decoder has an option to produce array tuples btw fq -o array=true . test.xml, not so nice for quick queries but is more consistent).
Also to properly convert/validate types etc a schema is needed i think, but you could do something like this but it might incorrectly convert string attributes or bodies.
<root>
<aitem>
<name>abc</name>
<value>123</value>
</aitem>
<bitem>
<name>bbbb</name>
<value>2222</value>
</bitem>
<bitem>
<name>BB</name>
<value>22</value>
</bitem>
<bitem>
<name>BB</name>
<value>true</value>
</bitem>
<bitem>
<name>BB</name>
<value>[1,2,3]</value>
</bitem>
</root>$ fq 'walk(if type == "object" then with_entries(.value |= if type == "object" then [.] end) else (fromjson? | scalars) // . end)' test.xml
{
"root": [
{
"aitem": [
{
"name": "abc",
"value": 123
}
],
"bitem": [
{
"name": "bbbb",
"value": 2222
},
{
"name": "BB",
"value": 22
},
{
"name": "BB",
"value": true
},
{
"name": "BB",
"value": "[1,2,3]"
}
]
}
]
}A more correct solution i guess would be if the xml decoder supported xsd schema as a option somehow. Would it also validation in addition to convert types?
Usage could be something like fq -o schema=@schema.xsd . file.xml (@ reads a string from a file) or from_xml({schema: <string with xsd>}). Don't really have any gut feeling how much work it would be and also how it would interact with the current xml decoder options.
Hope that helps and I can't give any promises that I will try to implement it, a bit busy with other fq and non-fq related things. Is it something you would like to look into? happy to give advice and review etc
Hi again, did you get anywhere with this? don't think i will work on this anytime soon
@wader , NO found any program to define data using schema
Could you please help construct the json output from fq command to satisfy Below conditions.
- Convert all numbers/floats/booleans without double quotes as non-string.
- Mandate below tree elements only to be array but not entire JSON.
.content.exportDeclaration.lineItems.itemCodes
.content.exportDeclaration.tracking - Mandate array for 'itemCodes' in 'lineItems' , should work any number of 'lineItems' sets dynamically.
<content>
<description>ITEMS</description>
<country>JAP</country>
<exportDeclaration>
<lineItems>
<number>1</number>
<description>PRODUCT A121</description>
<quantity>
<value>1600</value>
<unitOfMeasurement>PCS</unitOfMeasurement>
</quantity>
<itemCodes>
<typeCode>outbound</typeCode>
<value>82828282</value>
</itemCodes>
<exportReasonType>Permanent</exportReasonType>
<manufacturerCountry>MY</manufacturerCountry>
</lineItems>
<lineItems>
<number>2</number>
<description>PRODUCT B121</description>
<quantity>
<value>1800</value>
<unitOfMeasurement>PCS</unitOfMeasurement>
</quantity>
<itemCodes>
<typeCode>outbound</typeCode>
<value>84848484</value>
</itemCodes>
<exportReasonType>Permanent</exportReasonType>
<manufacturerCountry>MY</manufacturerCountry>
</lineItems>
<tracking>
<number>"123456789"</number>
<date>"2023-02-28"</date>
</tracking>
</exportDeclaration>
</content>
Hi, with the above jq expression i get this, how close is that? could you give an example of expected json output?
To do more complex things you would probably need to implement some kind of schema to describe the transformations, alternatively use a jq expression to describe the rewrites.
{
"content": [
{
"country": "JAP",
"description": "ITEMS",
"exportDeclaration": [
{
"lineItems": [
{
"description": "PRODUCT A121",
"exportReasonType": "Permanent",
"itemCodes": [
{
"typeCode": "outbound",
"value": 82828282
}
],
"manufacturerCountry": "MY",
"number": 1,
"quantity": [
{
"unitOfMeasurement": "PCS",
"value": 1600
}
]
},
{
"description": "PRODUCT B121",
"exportReasonType": "Permanent",
"itemCodes": [
{
"typeCode": "outbound",
"value": 84848484
}
],
"manufacturerCountry": "MY",
"number": 2,
"quantity": [
{
"unitOfMeasurement": "PCS",
"value": 1800
}
]
}
],
"tracking": [
{
"date": "2023-02-28",
"number": "123456789"
}
]
}
]
}
]
}@wader ,
Would like to see some command in FQ , one command to convert XML to JSON and accordingly Mandate ARRAYS.
Expected JSON Output
{
"content": {
"description": "ITEMS",
"country": "JAP",
"exportDeclaration": {
"lineItems": [
{
"number": 1,
"description": "PRODUCT A121",
"quantity": {
"value": 1600,
"unitOfMeasurement": "PCS"
},
"itemCodes": [
{
"typeCode": "outbound",
"value": 82828282
}
],
"exportReasonType": "Permanent",
"manufacturerCountry": "MY"
},
{
"number": 2,
"description": "PRODUCT B121",
"quantity": {
"value": 1800,
"unitOfMeasurement": "PCS"
},
"itemCodes": [
{
"typeCode": "outbound",
"value": 84848484
}
],
"exportReasonType": "Permanent",
"manufacturerCountry": "MY"
}
],
"tracking": [
{
"date": "2023-02-28",
"number": "123456789"
}
]
}
}
}
Is your expected JSON same as what i posted? looks very similar. That you can get by doing
fq 'walk(if type == "object" then with_entries(.value |= if type == "object" then [.] end) else (fromjson? | scalars) // . end)' test.xml
@wader ,
Expected JSON has only normally arrays or mandated arrays in square braces.
But we don't require arrayed square braces everywhere as like from above FQ expression.
-
Could you please just define below xml elements to be mandatory array.,
-
Mandate array for 'itemCodes' in 'lineItems' , should work on any number of 'lineItems' sets dynamically.
.content.exportDeclaration.lineItems.itemCodes
.content.exportDeclaration.tracking -
Enhance expression then to , Convert all numeric/floats/booleans as non-strings so that not double quoted.
Ok, sorry a bit busy with other things. But as it sounds like you want to do specific things based on key names etc so i would say jq itself is a good language to express those rules. Maybe try stackoverflow for a quicker answer and it's not really fq specific.
@wader ,
Could you help in below JQ expression which works for single lineItems set ,
But if multiple sets are there inside lineItems , its failing
(.content.exportDeclaration.tracking, .content.exportDeclaration.lineItems.itemCodes) |= [.]
Sample JSON
{
"content": {
"description": "ITEMS",
"country": "JAP",
"exportDeclaration": {
"lineItems": [
{
"number": 1,
"description": "PRODUCT A121",
"quantity": {
"value": 1600,
"unitOfMeasurement": "PCS"
},
"itemCodes":
{
"typeCode": "outbound",
"value": 82828282
},
"exportReasonType": "Permanent",
"manufacturerCountry": "MY"
},
{
"number": 2,
"description": "PRODUCT B121",
"quantity": {
"value": 1800,
"unitOfMeasurement": "PCS"
},
"itemCodes":
{
"typeCode": "outbound",
"value": 84848484
},
"exportReasonType": "Permanent",
"manufacturerCountry": "MY"
}
],
"tracking":
{
"date": "2023-02-28",
"number": "123456789"
}
}
}
}
Hi, try (.content.exportDeclaration.tracking, .content.exportDeclaration.lineItems[].itemCodes) |= [.], that is add a [] after lineItems so that it iterates the array. If some of the paths might be missing you can add ? like (...)? to ignore errors.