REG: how to extract json fields without writing loop query

Anonymous — Wed, 18 Sep 2019 11:48:46 GMT

Hi Team,

I have json files in a folder, i would like to migrate them to PostgreSQL.

I can use tfileinputjson -> tmap -> tpostgresoutput

But it's very hard for me to write loop query for each field, since it has 272 columns ! is there any way that i can make it happen

Thanks,

Prabuj

Re: REG: how to extract json fields without writing loop query

TRF — Wed, 18 Sep 2019 12:14:41 GMT

Can you share an extract of your JSON file with just 3 or 4 fields to be sure of what it looks like?

Re: REG: how to extract json fields without writing loop query

Anonymous — Thu, 19 Sep 2019 07:50:52 GMT

Here is the json file.

[
{
"Id": 1,
"TimeStamp": "13/9/2019 7:00:00 PM",
"PT_2158QR2001PV": 0.6602208,
"PT_21FC001PV": 1061.44177,
"PT_21FC048PV": 0.0,
"PT_21FC049PV": 9168.777,
"PT_21FC053PV": 149.812271,
"PT_21FC082PV": 41373.37,
"PT_21FC089PV": 40244.41,
"PT_21FC094PV": 6.76366,
"PT_21FC505PV": 584.019165,
"PT_21FC505XPV": 485.8792,
"PT_21FC524PV": 53.1628227,
"PT_21FC524XPV": 49.5450478,
"PT_21FC527PV": 155.081757,
"PT_21FC537PV": 29.90289,
"PT_21FC538PV": 368.8989,
"PT_21FC539PV": 195.39325,
"PT_21FC539XPV": 176.215851,
"PT_21FC541PV": 168.042679,
"PT_21FC543PV": 71.36874,
"PT_21FC548XPV": 0.0,
"PT_21FC549XPV": 381.243134,
"PT_21FC550PV": 50.3458748,
"PT_21FC554PV": 0.0,
"PT_21FC555PV": 0.0,
"PT_21FC580PV": 20857.0527,
"PT_21FC609PV": 18178.3965,
"PT_21FC616PV": 1.76296556,
"PT_21FC672PV": null,
"PT_21FC676PV": 4.51095772,
"PT_21FC695PV": 56.6318474,
"PT_21FI055PV": 279.6417,
"PT_21FI066PV": 4532.453,
"PT_21FI074PV": 10.0644579,
"PT_21FI612PV": null,
"PT_21FT048PV": 0.0,
"PT_21JDSCTP02PV": 269.0527,
"PT_21JTI086PV": 139.447174,
"PT_21PC010PV": 2.05063081,
"PT_21PC051PV": 2.05385971,
"PT_21PC117PV": 1.18454981,
"PT_21PC523PV": 1.83230186,
"PT_21PC550PV": 1.77605891,
"PT_21PI512PV": 17.38755,
"PT_21TC079PV": 9168.777,
"PT_21TC090PV": 56.4368362,
"PT_21TC598PV": 184.719223,
"PT_21TC621PV": 68.7611847,
"PT_21TC622PV": 81.47314,
"PT_21TI010PV": 156.810333,
"PT_21TI068PV": 231.408554,
"PT_21TI080PV": 182.136459,
"PT_21TI082PV": 33.5023956,
"PT_21TI083PV": 179.184753,
"PT_21TI087PV": 113.423264,
"PT_21TI088PV": 117.561722,
"PT_21TI089PV": 81.26975,
"PT_21TI091PV": 56.26687,
"PT_21TI094PV": 50.9827957,
"PT_21TI095PV": 37.167202,
"PT_21TI096PV": 46.23876,
"PT_21TI101PV": 53.8636932,
"PT_21TI102PV": 75.33385,
"PT_21TI103PV": 352.046143,
"PT_21TI184PV": 155.696625,
"PT_21TI186PV": 90.97234,
"PT_21TI187PV": 268.961731,
"PT_21TI227PV": 32.752594,
"PT_21TI505PV": 47.26126,
"PT_21TI540PV": 262.46524,
"PT_21TI543PV": 359.454651,
"PT_21TI544PV": 149.394424,
"PT_21TI556PV": 42.5098534,
"PT_21TI558PV": 86.07119,
"PT_21TI559PV": 63.58007,
"PT_21TI565PV": 204.321289,
"PT_21TI570PV": 177.849579,
"PT_21TI582PV": 228.243073,
"PT_21TI589PV": 211.385162,
"PT_21TI590PV": 87.39093,
"PT_21TI619PV": 187.1612,
"PT_21TI626PV": null,
"PT_21TI627PV": 34.06986,
"PT_21TI628PV": 34.46137,
"PT_21TI678PV": 199.040314,
"PT_21TI685PV": 184.1577,
"PT_21TI686PV": 188.56398,
"PT_21TI704PV": 148.132385,
"PT_21TI705PV": 198.354614,
"PT_21XI010APV": 10.9328928,
"PT_21XI010PV": 11.3484449,
"PT_21XI011PV": 10.5609112,
"PT_21XI507PV": 10.566205,
"PT_21XI508PV": 10.6491785,
"PT_24TI183PV": 254.25264,
"PT_24TI184PV": 202.053314,
"PT_21FC003PV": 575.909241,
"PT_21FC014PV": 162.325821,
"PT_21FC015PV": 320.090851,
"PT_21FC057PV": 284.162476,
"PT_21FC057XPV": 249.663971,
"PT_21FC058PV": 53.5607872,
"PT_21FC058XPV": 45.1464958,
"PT_21FC061PV": 727.8579,
"PT_21FC185PV": 1647.65234,
"PT_21FC202PV": 33.26484,
"PT_21FC205PV": 105.58886,
"PT_21FC207PV": 162.3716,
"PT_21FC209PV": 24.0887985,
"PT_21FC211PV": 535.5941,
"PT_21FC213PV": 701.2,
"PT_21FC269PV": 1411.96082,
"PT_21FC540PV": 141.961609,
"PT_21FC541XPV": 154.347443,
"PT_21FC544PV": 68.00997,
"PT_21FC548PV": 0.0,
"PT_21FC549PV": 440.503448,
"PT_21FC551PV": 160.728683,
"PT_21FC551XPV": 148.714844,
"PT_21FC556PV": 223.173,
"PT_21FC556XPV": 207.893219,
"PT_21FC669PV": 134.294373,
"PT_21FC673PV": 59.40386,
"PT_21FC684PV": 81.42837,
"PT_21FC685PV": 26.7701664,
"PT_21FC686PV": 28.8195343,
"PT_21FC687PV": 257.183075,
"PT_21FC691PV": 96.45074,
"PT_21FC692PV": 0.0,
"PT_21FI201PV": 418.131836,
"PT_21FI627PV": 294.712372,
"PT_21FI668PV": 357.1876,
"PT_21FT001PV": 1163.49768,
"PT_21TC547PV": 349.740936,
"PT_21TI001PV": 73.77034,
"PT_21TI002PV": 40.214325,
"PT_21TI004PV": 124.136475,
"PT_21TI005PV": 108.985809,
"PT_21TI009PV": 157.141525,
"PT_21TI018PV": 169.813461,
"PT_21TI019PV": 128.0708,
"PT_21TI020PV": 181.883789,
"PT_21TI023PV": 214.624283,
"PT_21TI024PV": 273.378479,
"PT_21TI025PV": 200.068253,
"PT_21TI026PV": 203.909546,
"PT_21TI070PV": 111.360649,
"PT_21TI072PV": 191.922928,
"PT_21TI073PV": 279.6676,
"PT_21TI081PV": 276.768555,
"PT_21TI084PV": 343.612762,
"PT_21TI086PV": 139.534012,
"PT_21TI170PV": 51.704277,
"PT_21TI171PV": 130.091583,
"PT_21TI172PV": 127.650604,
"PT_21TI174PV": 149.121033,
"PT_21TI176PV": 245.15657,
"PT_21TI177PV": 252.9207,
"PT_21TI178PV": 242.53801,
"PT_21TI179PV": 197.601181,
"PT_21TI180PV": 251.822021,
"PT_21TI181PV": 228.8317,
"PT_21TI528PV": 219.518234,
"PT_21TI529PV": 278.2458,
"PT_21TI545PV": 242.700211,
"PT_21TI546PV": 316.730835,
"PT_21TI561PV": 167.30899,
"PT_21TI564PV": 219.321564,
"PT_21TI567PV": 131.742508,
"PT_21TI569PV": 195.845123,
"PT_21TI571PV": 214.5063,
"PT_21TI572PV": 236.546616,
"PT_21TI575PV": 127.674377,
"PT_21TI581PV": 260.62973,
"PT_21TI584PV": 243.824554,
"PT_21TI585PV": 84.0819,
"PT_21TI586PV": 143.364,
"PT_21TI588PV": 247.50946,
"PT_21TI592PV": 260.8351,
"PT_21TI596PV": 184.600143,
"PT_21TI597PV": 302.10733,
"PT_21TI629PV": 297.404724,
"PT_21TI630PV": 168.886536,
"PT_21TI631PV": 252.1481,
"PT_21TI632PV": 233.378784,
"PT_21TI633PV": 176.656464,
"PT_21TI634PV": 256.161865,
"PT_21TI635PV": 246.7713,
"PT_21TI636PV": 274.4964,
"PT_21TI679.PV": 270.7338,
"PT_21TI680PV": 127.1425,
"PT_21TI681PV": 249.6808,
"PT_21TI682PV": 256.0195,
"PT_21TI683PV": 263.484436,
"PT_21TI687PV": 294.52533,
"PT_21TI688PV": 172.298859,
"PT_21TI689PV": 63.04208,
"PT_21TI691PV": 123.045036,
"PT_21TI692PV": 157.6052,
"PT_21TI693PV": 261.263855,
"PT_21TI694PV": 268.465973,
"PT_21TI696PV": 155.414749,
"PT_21TI697PV": 150.1182,
"PT_21TI698PV": 125.541374,
"PT_21TI699PV": 205.541367,
"PT_21TI700PV": 223.971634,
"PT_21UV152OP": 0.0,
"PT_21UV193OP": -5.0,
"PT_21UV196OP": -5.0,
"PT_21UV282OP": -5.0,
"PT_24FC013PV": 301.7704,
"PT_24FC019PV": 374.922546,
"PT_21FC016PV": 133.131378,
"PT_21FC017PV": 135.187958,
"PT_21FC018PV": 132.089432,
"PT_21FC019PV": 136.532623,
"PT_21FC020PV": 134.16423,
"PT_21FC021PV": 135.816727,
"PT_21FC022PV": 134.115219,
"PT_21FC023PV": 133.450485,
"PT_21FC217PV": 108.4136,
"PT_21FC218PV": 101.765961,
"PT_21FC219PV": 103.74099,
"PT_21FC220PV": 70.12254,
"PT_21FC506PV": 91.3325348,
"PT_21FC507PV": 84.9617844,
"PT_21FC508PV": 85.85876,
"PT_21FC509PV": 87.74579,
"PT_21FC510PV": 83.34798,
"PT_21FC511PV": 89.40988,
"PT_21FC512PV": 84.2790756,
"PT_21FC513PV": 89.49009,
"PT_21FC561PV": 1976.15051,
"PT_21FC607PV": 1707.68774,
"PT_21FI255PV": 4321.79,
"PT_21FT084PV": 3709.04956,
"PT_21FT087PV": 4108.22461,
"PT_21QI001PV": 2.1780417,
"PT_21QI051BPV": 2.652252,
"PT_21QI501PV": 2.75620556,
"PT_21TC035PV": 356.994415,
"PT_21TC218PV": 350.4736,
"PT_21TC527PV": 384.5408,
"PT_21TI028PV": 269.9143,
"PT_21TI115PV": 307.760834,
"PT_21TI119PV": 33.5911674,
"PT_21TI189PV": 267.093964,
"PT_21TI508PV": 345.159454,
"PT_21TI601PV": 32.36565,
"PT_58SCV582011BUTENE": 0.0,
"PT_58SCV58201ACETYLENE": 0.0,
"PT_58SCV58201C1": 37.63,
"PT_58SCV58201C2": 10.01,
"PT_58SCV58201C3": 6.82,
"PT_58SCV58201CIS2BUTENE": 0.0,
"PT_58SCV58201CO": 0.0,
"PT_58SCV58201CO2": 0.88,
"PT_58SCV58201DENS15": 0.6974,
"PT_58SCV58201H2": 37.52,
"PT_58SCV58201IC4": 2.77,
"PT_58SCV58201IC5": 0.66,
"PT_58SCV58201MOLWTC": 17.46,
"PT_58SCV58201N2": 0.08,
"PT_58SCV58201NC4": 1.71,
"PT_58SCV58201NC5": 0.26,
"PT_58SCV58201NC6Plus": 0.71,
"PT_58SCV58201PROPYLENE": 0.52,
"PT_58SCV58201TRANS2BUTENE": 0.05,
"PT_58TI2010DACAPV": 36.588932,
"PT_21TC008PV": 125.557343,
"PT_21TC173PV": 127.051414
}
]

topic Re: REG: how to extract json fields without writing loop query in Talend Studio

REG: how to extract json fields without writing loop query

Re: REG: how to extract json fields without writing loop query

Re: REG: how to extract json fields without writing loop query