I have two files, they are very large so I will give a snippet of relevant data:
from_file:
[
{
"id": 7212486534162308,
"rowNumber": 283,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Dd Kk",
"linkInFromCell": {
"status": "OK",
"sheetId": 4409864198875012,
"rowId": 5483738575202180,
"columnId": 6482434207311748,
"sheetName": "INFORMATION - J-1000579"
},
"value": "Dd Kk"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Dd Kk link",
"hyperlink": {
"url": "https://app.test.com/fvffvs/dfesfrfr"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 6836472364722052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Dd Kk"
}
]
},
{
"id": 7046413570598788,
"rowNumber": 284,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Macquarie Education Programs",
"linkInFromCell": {
"status": "OK",
"sheetId": 8275059887368068,
"rowId": 8307181892986756,
"columnId": 687741640959876,
"sheetName": "INFORMATION - J-1000532.01"
},
"value": "Macquarie Education Programs"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Macquarie Education Programs Building - J-10005",
"hyperlink": {
"url": "https://app.test.com/jnjnnlkm/fvfbsfbfgvdsfe"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 4795289157298052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Macquarie Education Programs - J-10005"
}
]
}
]
to_file:
[
{
"id": 977325251291012,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"value": ""
},
{
"columnId": 5460306864433028,
"value": "Dd Kk",
"displayValue": "Dd Kk"
}
]
},
{
"id": 5480924878661508,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300
},
{
"columnId": 5460306864433028,
"value": "Macquarie Education Programs",
"displayValue": "Macquarie Education Programs"
}
]
}
]
in the to_file, inside each object that contains "columnId": 5206337428711300 I need to add all the key value pairs (except for "Column" and "columnId") from the from_file "columnId": 5822630258337668 containing object where that array of cell's "columnId": 7408953102493572 containing object has the same "displayValue" value as the to_file's "columnId": 5460306864433028 containing objects "displayValue" value.
desired outcome below:
[
{
"id": 977325251291012,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"displayValue": "Dd Kk link",
"hyperlink": {
"url": "https://app.test.com/fvffvs/dfesfrfr"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 6836472364722052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Dd Kk"
},
{
"columnId": 5460306864433028,
"value": "Dd Kk",
"displayValue": "Dd Kk"
}
]
},
{
"id": 5480924878661508,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"displayValue": "Macquarie Education Programs Building - J-10005",
"hyperlink": {
"url": "https://app.test.com/jnjnnlkm/fvfbsfbfgvdsfe"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 4795289157298052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Macquarie Education Programs - J-10005"
},
{
"columnId": 5460306864433028,
"value": "Macquarie Education Programs",
"displayValue": "Macquarie Education Programs"
}
]
}
]
Thanks
@PMF answer below works but will fail if there is an item in the to_file that can't be found in the from file.
this works Demo
[
{
"id": 7660296628791172,
"rowNumber": 484,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "CSNSW",
"linkInFromCell": {
"status": "OK",
"sheetId": 3495076589528964,
"rowId": 4453675823982468,
"columnId": 6210919910926212,
"sheetName": "INFORMATION - J-1000739.01"
},
"value": "CSNSW"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "CSNSW - J-1000739.01",
"hyperlink": {
"url": "https://app.test.com/beffvfdcwe/ergrsvsrfv"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 3495076589528964,
"rowId": 4655985963493252,
"columnId": 6210919910926212,
"sheetName": "INFORMATION - J-1000739.01"
},
"value": "CSNSW - J-1000739.01"
}
]
},
{
"id": 7212486534162308,
"rowNumber": 283,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Dd Kk",
"linkInFromCell": {
"status": "OK",
"sheetId": 4409864198875012,
"rowId": 5483738575202180,
"columnId": 6482434207311748,
"sheetName": "INFORMATION - J-1000579"
},
"value": "Dd Kk"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Dd Kk link",
"hyperlink": {
"url": "https://app.test.com/fvffvs/dfesfrfr"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 6836472364722052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Dd Kk"
}
]
},
{
"id": 7046413570598788,
"rowNumber": 284,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Macquarie Education Programs",
"linkInFromCell": {
"status": "OK",
"sheetId": 8275059887368068,
"rowId": 8307181892986756,
"columnId": 687741640959876,
"sheetName": "INFORMATION - J-1000532.01"
},
"value": "Macquarie Education Programs"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Macquarie Education Programs Building - J-10005",
"hyperlink": {
"url": "https://app.test.com/jnjnnlkm/fvfbsfbfgvdsfe"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 4795289157298052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Macquarie Education Programs - J-10005"
}
]
}
]
[
{
"id": 977325251291012,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"value": ""
},
{
"columnId": 5460306864433028,
"value": "Dd Kk",
"displayValue": "Dd Kk"
}
]
},
{
"id": 5480924878661508,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300
},
{
"columnId": 5460306864433028,
"value": "Macquarie Education Programs",
"displayValue": "Macquarie Education Programs"
}
]
}
]
this doesn't work Demo
[
{
"id": 7660296628791172,
"rowNumber": 484,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "CSNSW",
"linkInFromCell": {
"status": "OK",
"sheetId": 3495076589528964,
"rowId": 4453675823982468,
"columnId": 6210919910926212,
"sheetName": "INFORMATION - J-1000739.01"
},
"value": "CSNSW"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "CSNSW - J-1000739.01",
"hyperlink": {
"url": "https://app.test.com/beffvfdcwe/ergrsvsrfv"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 3495076589528964,
"rowId": 4655985963493252,
"columnId": 6210919910926212,
"sheetName": "INFORMATION - J-1000739.01"
},
"value": "CSNSW - J-1000739.01"
}
]
},
{
"id": 7212486534162308,
"rowNumber": 283,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Dd Kk",
"linkInFromCell": {
"status": "OK",
"sheetId": 4409864198875012,
"rowId": 5483738575202180,
"columnId": 6482434207311748,
"sheetName": "INFORMATION - J-1000579"
},
"value": "Dd Kk"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Dd Kk link",
"hyperlink": {
"url": "https://app.test.com/fvffvs/dfesfrfr"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 6836472364722052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Dd Kk"
}
]
},
{
"id": 7046413570598788,
"rowNumber": 284,
"cells": [
{
"Column": "SS name",
"columnId": 7408953102493572,
"displayValue": "Macquarie Education Programs",
"linkInFromCell": {
"status": "OK",
"sheetId": 8275059887368068,
"rowId": 8307181892986756,
"columnId": 687741640959876,
"sheetName": "INFORMATION - J-1000532.01"
},
"value": "Macquarie Education Programs"
},
{
"Column": "link",
"columnId": 5822630258337668,
"displayValue": "Macquarie Education Programs Building - J-10005",
"hyperlink": {
"url": "https://app.test.com/jnjnnlkm/fvfbsfbfgvdsfe"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 4795289157298052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Macquarie Education Programs - J-10005"
}
]
}
]
[
{
"id": 977325251291012,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"value": ""
},
{
"columnId": 5460306864433028,
"value": "Dd Kk",
"displayValue": "Dd Kk"
}
]
},
{
"id": 5480924878661508,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300
},
{
"columnId": 5460306864433028,
"value": "Macquarie Education Programs",
"displayValue": "Macquarie Education Programs"
}
]
},
{
"id": 7276986526263172,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300
},
{
"columnId": 5460306864433028,
"value": "Dubbo",
"displayValue": "Dubbo"
}
]
}
]
Thanks!!
CodePudding user response：
You could build an INDEX on a matching displayValue, and then JOIN the arrays by updating = the right object in to.json with the corresponding object in from.json:
jq '
[
JOIN(
INDEX(.[].cells; .[] | select(.columnId == 7408953102493572).displayValue);
input[];
.cells[] | select(.columnId == 5460306864433028).displayValue;
(.[0].cells[] | select(.columnId == 5206337428711300)) =
(.[1][] | select(.columnId == 5822630258337668) | del(.Column, .columnId))
)[0]
]
' from.json to.json
[
{
"id": 977325251291012,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"value": "Dd Kk",
"displayValue": "Dd Kk link",
"hyperlink": {
"url": "https://app.test.com/fvffvs/dfesfrfr"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 6836472364722052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
}
},
{
"columnId": 5460306864433028,
"value": "Dd Kk",
"displayValue": "Dd Kk"
}
]
},
{
"id": 5480924878661508,
"cells": [
{
"columnId": 2040705442965380,
"value": true
},
{
"columnId": 5206337428711300,
"displayValue": "Macquarie Education Programs Building - J-10005",
"hyperlink": {
"url": "https://app.test.com/jnjnnlkm/fvfbsfbfgvdsfe"
},
"linkInFromCell": {
"status": "OK",
"sheetId": 316850291664772,
"rowId": 4795289157298052,
"columnId": 6189414452881284,
"sheetName": "PROJECT INTAKE SHEET"
},
"value": "Macquarie Education Programs - J-10005"
},
{
"columnId": 5460306864433028,
"value": "Macquarie Education Programs",
"displayValue": "Macquarie Education Programs"
}
]
}
]