jq substr() equivalent to format a value

user_1225434 0
Viewed 2516

Hope you are fine in these times.

I am floored with a problem and I hope you have more knowledge.

Using 'jq' I want to change all the dates from eg. 19731013 (string) to a 1973-10-13

[
    {
        "Mail": "john@example.com",
        "Name": "Smith",
        "Employee_Number": "000555",
        "First_Name": "John",
        "Company": "ACME",
        "Department": null,
        "Employment_Status": "Retiree",
        "Start_Date": "19770516",
        "Function_Start_Date": "19770516",
        "Group_Phone": "",
        "Job_Title": "Operations Manager Warehousing",
        "Sub_Group": "Exempts",
        "Location": "Tibuktu",
        "Organizational_Unit": null,
        "Date_of_Birth": "19560719",
        "Gender": "1"
    },
    {
        "Mail": "mary@example.com",
        "Name": "Smith",
        "Employee_Number": "000777",
        "First_Name": "Mary",
        "Company": "ACME",
        "Department": null,
        "Employment_Status": "Retiree",
        "Start_Date": "19770516",
        "Function_Start_Date": "19770516",
        "Group_Phone": "",
        "Job_Title": "Manager",
        "Sub_Group": "Exempts",
        "Location": "Tibuktu",
        "Organizational_Unit": null,
        "Date_of_Birth": "19560719",
        "Gender": "2"
    }
]

Is it possible to use substr(.Start_Date,1,5)"-"substr(.Start_Date,6,2)"-"substr(.Start_Date,8,3) like with awk in a CSV?

Maybe I am staring at a wall while missing the door on the right?

UPDATE: Thanks a lot guys this worked like a charm!

jq -r '.[].Start_Date |= "\(.[0:4])-\(.[4:6])-\(.[6:8])" | .[].Function_Start_Date |= "\(.[0:4])-\(.[4:6])-\(.[6:8])" | .[].Date_of_Birth|="\(.[0:4])-\(.[4:6])-\(.[6:8])"' employees.json > test.json
jq substr slice json string
2 Answers
user_102486780

There is also regex match function in jq using capture that emits named captured groups, which can be later joined by - to form the date strings like you wanted.

jq '.[].Start_Date | capture("(?<x>[0-9]{4})(?<y>[0-9]{2})(?<z>[0-9]{2})") | join("-")'

This is assuming, your Start_Date field is atleast 8 chars long and does not validate for lengths lesser than that.

user_52910150
Related