--- title: "Extracting raw JSON data" slug: "pcap2json-extracting-raw-json-data" updated: 2025-03-14T14:36:20Z published: 2025-03-14T14:36:20Z canonical: "docs.fmad.io/pcap2json-extracting-raw-json-data" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.fmad.io/llms.txt > Use this file to discover all available pages before exploring further. # Extracting raw JSON data Extracting the raw data of PCAP2JSON can be helpful for debugging and general customized analysis tools. The high level dataflow is described below. ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(90).png?sv=2026-02-06&spr=https&st=2026-11-13T17%3A44%3A05Z&se=2026-11-13T18%3A44%3A05Z&sr=c&sp=r&sig=AiEpp2npC50qrNJLc83uirhp9oN7t3kQCjunQrEZtsY%3D) In many cases it can be helpful to extract JSON data directly out of the Clickhouse Database (5) in the diagram above. This results in the following additional workflow of (8), shown below. ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(94).png?sv=2026-02-06&spr=https&st=2026-11-13T17%3A44%3A05Z&se=2026-11-13T18%3A44%3A05Z&sr=c&sp=r&sig=AiEpp2npC50qrNJLc83uirhp9oN7t3kQCjunQrEZtsY%3D) ### Step 1) craft an SQL query The first step is to create an SQL query with JSON formatting. This is done inside the PCAP2JSON LXC Container. In the path shown below, create a file `pcap2json-export-json.sql` as shown below ```plaintext root@fmadio100v2-228U-pcap2json:~/pcap2json# vi pcap2json-export-json.sql ``` NOTE: this is running inside the LXC container so there is a green prompt ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(92).png?sv=2026-02-06&spr=https&st=2026-11-13T17%3A44%3A05Z&se=2026-11-13T18%3A44%3A05Z&sr=c&sp=r&sig=AiEpp2npC50qrNJLc83uirhp9oN7t3kQCjunQrEZtsY%3D) ### Step 2) Full database dump in JSON The most simple export is without any filtering, paste the contents of the file similar to ```plaintext select * from pcapflow.meta_1sec limit 100 format JSONEachRow ``` Full list of output formats is found here [https://clickhouse.com/docs/en/interfaces/formats](https://clickhouse.com/docs/en/interfaces/formats) NOTE: the limt 100 will only output 100 rows, delete this if you want to export the entire database ### Step 3) run the SQL script Command below ```plaintext cat pcap2json-export-json.sql  | clickhouse-client  -mn ``` Example output ```json root@fmadio100v2-228U-pcap2json:~/pcap2json# cat pcap2json-export-json.sql  | clickhouse-client  -mn                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            : Number of CPUs detected is not deterministic. Per-CPU arena disabled. {"timestamp":"2024-11-10 10:00:06","site":"","system":"fmadio100v2-228U","hashHalf":[0,0,0,0,0],"macSrc":"00:0c:29:79:d5:b8","macDst":"f8:f2:1e:bc:cb:61","macProto":"ARP","ipv4Src":"0.0.0.0","ipv4Dst":"0.0.0.0","ipv4Proto":"","ipv4DSCP":"","vlan0":"4080","vlan1":"","mpls0TC":"none","portSrc":"","portDst":"","hostSrc":"0.0.0.0","hostDst":"0.0.0.0","geoipSrc":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"geoipDst":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"flowTypeSrc":"public-internet","flowTypeDst":"public-internet","flowSubTypeSrc":"","flowSubTypeDst":"","deviceSrc":"00:0c:29:79:d5:b8","deviceDst":"sg2-jump0-wg-gw","tapaggDevice":"","tapaggVlan":"4080","tapaggVlanInv":"4081","linkType":"","linkSubType":"","linkSwitchName":"","linkInterface":"","linkDesc":"","linkBandwidth":0,"vlanInner":"","vlanInnerDesc":"","tcpFin":0,"tcpSyn":0,"tcpSynAck":0,"tcpSackPerm":0,"tcpRst":0,"tcpSack":0,"tcpZeroWindow":0,"sumPacket":"1","sumByte":"68","sumFCS":"0","meanPacket":"1","meanByte":"68","meanFCS":"0","maxPacket":"1","maxByte":"68","maxFCS":"0"} {"timestamp":"2024-11-10 10:00:06","site":"","system":"fmadio100v2-228U","hashHalf":[0,0,0,0,0],"macSrc":"00:0c:29:79:d5:b8","macDst":"f8:f2:1e:bc:cb:61","macProto":"ARP","ipv4Src":"0.0.0.0","ipv4Dst":"0.0.0.0","ipv4Proto":"","ipv4DSCP":"","vlan0":"4080","vlan1":"","mpls0TC":"none","portSrc":"","portDst":"","hostSrc":"0.0.0.0","hostDst":"0.0.0.0","geoipSrc":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"geoipDst":{"country_name":"","country_iso_code":"\u0000\u0000","longitude":0,"latitude":0,"city_name":"","isp":"","org":"","asn":"0","asn_org":""},"flowTypeSrc":"public-internet","flowTypeDst":"public-internet","flowSubTypeSrc":"","flowSubTypeDst":"","deviceSrc":"00:0c:29:79:d5:b8","deviceDst":"sg2-jump0-wg-gw","tapaggDevice":"","tapaggVlan":"4080","tapaggVlanInv":"4081","linkType":"","linkSubType":"","linkSwitchName":"","linkInterface":"","linkDesc":"","linkBandwidth":0,"vlanInner":"","vlanInnerDesc":"","tcpFin":0,"tcpSyn":0,"tcpSynAck":0,"tcpSackPerm":0,"tcpRst":0,"tcpSack":0,"tcpZeroWindow":0,"sumPacket":"1","sumByte":"68","sumFCS":"0","meanPacket":"1","meanByte":"68","meanFCS":"0","maxPacket":"1","maxByte":"68","maxFCS":"0"} . . ``` Or pipe it thru `jq` for a more human readable format ```plaintext cat pcap2json-export-json.sql  | clickhouse-client  -mn | jq ``` Example output ```json root@fmadio100v2-228U-pcap2json:~/pcap2json# cat pcap2json-export-json.sql  | clickhouse-client  -mn | jq {  "timestamp": "2024-11-10 10:00:06",  "site": "",  "system": "fmadio100v2-228U",  "hashHalf": [    0,    0,    0,    0,    0  ],  "macSrc": "00:0c:29:79:d5:b8",  "macDst": "f8:f2:1e:bc:cb:61",  "macProto": "ARP",  "ipv4Src": "0.0.0.0",  "ipv4Dst": "0.0.0.0",  "ipv4Proto": "",  "ipv4DSCP": "",  "vlan0": "4080",  "vlan1": "",  "mpls0TC": "none",  "portSrc": "",  "portDst": "",  "hostSrc": "0.0.0.0",  "hostDst": "0.0.0.0",  "geoipSrc": {    "country_name": "",    "country_iso_code": "\u0000\u0000",    "longitude": 0,    "latitude": 0,    "city_name": "",    "isp": "",    "org": "",    "asn": "0",    "asn_org": ""  },  "geoipDst": {    "country_name": "",    "country_iso_code": "\u0000\u0000",    "longitude": 0,    "latitude": 0,    "city_name": "",    "isp": "",    "org": "",    "asn": "0",    "asn_org": ""  },  "flowTypeSrc": "public-internet",  "flowTypeDst": "public-internet",  "flowSubTypeSrc": "",  "flowSubTypeDst": "",  "deviceSrc": "00:0c:29:79:d5:b8",  "deviceDst": "sg2-jump0-wg-gw",  "tapaggDevice": "",  "tapaggVlan": "4080",  "tapaggVlanInv": "4081",  "linkType": "",  "linkSubType": "",  "linkSwitchName": "",  "linkInterface": "",  "linkDesc": "",  "linkBandwidth": 0,  "vlanInner": "",  "vlanInnerDesc": "",  "tcpFin": 0,  "tcpSyn": 0,  "tcpSynAck": 0,  "tcpSackPerm": 0,  "tcpRst": 0,  "tcpSack": 0,  "tcpZeroWindow": 0,  "sumPacket": "1",  "sumByte": "68",  "sumFCS": "0",  "meanPacket": "1",  "meanByte": "68",  "meanFCS": "0",  "maxPacket": "1",  "maxByte": "68",  "maxFCS": "0" } . . ```