---
title: "Extract JSON data with Python"
slug: "extract-json-data-with-python"
updated: 2022-02-16T20:23:37Z
published: 2022-02-16T20:23:37Z
canonical: "docs.datajet.app/extract-json-data-with-python"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.datajet.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Extract JSON data with Python

## Overview

We have a database with a wide continuous string containing diverse information.

Some of this data is in the form of small JSON strings, we want to extract certain keys from this to create new fields.

**Actions**

- Make a DataSet field of info records that start with a { character
- Use this as a filter in an Expression that is simply a copy of the info field.
- This reduces the size of the data to discrete - and so we can use the programmed field functions
- Using [Programmed Field](/v1/docs/programmed-field), execute Python code to extract each key and create a new field.

## Example Python Code

```python
import sys
import json

#input and output files from first 2 arguments  
inputfile = sys.argv[1]
outputfile = sys.argv[2]
countsfile = sys.argv[3]

#read input file into array
iFile = open(inputfile, 'r') 
inputArray = iFile.read().splitlines() 
iFile.close()

    

oFile = open(outputfile, 'w') #write to file
for line in inputArray:
#
    try:
        #process the lines here
        if line.startswith("{") :
            #json string in this example has ' replacing "
            line = line.replace("'","\"")
            parsed_json = json.loads(line)
            ##extracting the key messageId
            processed=str(parsed_json['messageId'])
        else:
            processed=""
        
        
    except:
        processed="" #assign to null on exception
#endif    
#
    oFile.write(processed+'\n') 
oFile.close()
```

## Video

[Video](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/ReduceExtract.mp4)

## Example Script

```json
[
  {
    "method": "SaveDataSetAsField",
    "name": "Has Json",
    "dataSet": {
      "logic": "or",
      "name": "HasJson",
      "strict": true,
      "set": [
        {
          "logic": "and",
          "stype": "FIELD",
          "entity": {
            "type": "field",
            "name": "CompanyAndInfo.info",
            "table": "CompanyAndInfo",
            "valueFilter": [
            ]
          },
          "op": "like",
          "values": [
            "{*"
          ]
        }
      ]
    },
    "project": "ExtractJSON"
  },
  {
    "method": "BuildBakedField",
    "name": "Info With JSON",
    "project": "ExtractJSON",
    "targetTable": "CompanyAndInfo",
    "overwrite": true,
    "dataType": "default",
    "function": "dmath",
    "p1": "expression",
    "p2": "[info]",
    "nullIsZero": false,
    "NOORD": false,
    "dataSet": {
      "logic": "or",
      "name": "Has Json 1",
      "strict": true,
      "set": [
        {
          "logic": "or",
          "stype": "FIELD",
          "entity": {
            "type": "field",
            "name": "CompanyAndInfo.Has Json",
            "table": "CompanyAndInfo",
            "valueFilter": [
            ]
          },
          "op": "=",
          "values": [
            "1"
          ]
        }
      ]
    }
  },
  {
    "method": "BuildBakedField",
    "targetTable": "CompanyAndInfo",
    "overwrite": true,
    "name": "extracted-message",
    "function": "PyDiscreteProc",
    "source": "Info With JSON",
    "code": "696D706F7274207379730A696D706F7274206A736F6E0A0A23696E70757420616E64206F75747075742066696C65732066726F6D206669727374203220617267756D656E747320200A696E70757466696C65203D207379732E617267765B315D0A6F757470757466696C65203D207379732E617267765B325D0A636F756E747366696C65203D207379732E617267765B335D0A0A237265616420696E7075742066696C6520696E746F2061727261790A6946696C65203D206F70656E28696E70757466696C652C2027722729200A696E7075744172726179203D206946696C652E7265616428292E73706C69746C696E65732829200A6946696C652E636C6F736528290A0A202020200A0A6F46696C65203D206F70656E286F757470757466696C652C20277727292023777269746520746F2066696C650A666F72206C696E6520696E20696E70757441727261793A0A230A202020207472793A0A20202020202020202370726F6365737320746865206C696E657320686572650A20202020202020206966206C696E652E7374617274737769746828227B2229203A0A202020202020202020202020236A736F6E20737472696E6720696E2074686973206578616D706C65206861732027207265706C6163696E6720220A2020202020202020202020206C696E65203D206C696E652E7265706C616365282227222C225C2222290A2020202020202020202020207061727365645F6A736F6E203D206A736F6E2E6C6F616473286C696E65290A202020202020202020202020232365787472616374696E6720746865206B6579206D65737361676549640A20202020202020202020202070726F6365737365643D737472287061727365645F6A736F6E5B276D6573736167654964275D290A2020202020202020656C73653A0A20202020202020202020202070726F6365737365643D22220A20202020202020200A20202020202020200A202020206578636570743A0A202020202020202070726F6365737365643D2222202361737369676E20746F206E756C6C206F6E20657863657074696F6E0A23656E646966202020200A230A202020206F46696C652E77726974652870726F6365737365642B275C6E2729200A6F46696C652E636C6F736528290A2020202020202020202020202020202020202020200A",
    "project": "ExtractJSON"
  },
  {
    "method": "BuildBakedField",
    "targetTable": "CompanyAndInfo",
    "overwrite": true,
    "name": "extracted-date",
    "function": "PyDiscreteProc",
    "source": "Info With JSON",
    "code": "696D706F7274207379730A696D706F7274206A736F6E0A0A23696E70757420616E64206F75747075742066696C65732066726F6D206669727374203220617267756D656E747320200A696E70757466696C65203D207379732E617267765B315D0A6F757470757466696C65203D207379732E617267765B325D0A636F756E747366696C65203D207379732E617267765B335D0A0A237265616420696E7075742066696C6520696E746F2061727261790A6946696C65203D206F70656E28696E70757466696C652C2027722729200A696E7075744172726179203D206946696C652E7265616428292E73706C69746C696E65732829200A6946696C652E636C6F736528290A0A202020200A0A6F46696C65203D206F70656E286F757470757466696C652C20277727292023777269746520746F2066696C650A666F72206C696E6520696E20696E70757441727261793A0A230A202020207472793A0A20202020202020202370726F6365737320746865206C696E657320686572650A20202020202020206966206C696E652E7374617274737769746828227B2229203A0A202020202020202020202020236A736F6E20737472696E6720696E2074686973206578616D706C65206861732027207265706C6163696E6720220A2020202020202020202020206C696E65203D206C696E652E7265706C616365282227222C225C2222290A2020202020202020202020207061727365645F6A736F6E203D206A736F6E2E6C6F616473286C696E65290A202020202020202020202020232365787472616374696E6720746865206B6579206D65737361676549640A20202020202020202020202070726F6365737365643D737472287061727365645F6A736F6E5B2764617465275D290A2020202020202020656C73653A0A20202020202020202020202070726F6365737365643D22220A20202020202020200A20202020202020200A202020206578636570743A0A202020202020202070726F6365737365643D2222202361737369676E20746F206E756C6C206F6E20657863657074696F6E0A23656E646966202020200A230A202020206F46696C652E77726974652870726F6365737365642B275C6E2729200A6F46696C652E636C6F736528290A2020202020202020202020202020202020202020200A",
    "project": "ExtractJSON"
  },
  {
    "method": "BuildBakedField",
    "targetTable": "CompanyAndInfo",
    "overwrite": true,
    "name": "extracted-sale",
    "function": "PyDiscreteProc",
    "source": "Info With JSON",
    "code": "696D706F7274207379730A696D706F7274206A736F6E0A0A23696E70757420616E64206F75747075742066696C65732066726F6D206669727374203220617267756D656E747320200A696E70757466696C65203D207379732E617267765B315D0A6F757470757466696C65203D207379732E617267765B325D0A636F756E747366696C65203D207379732E617267765B335D0A0A237265616420696E7075742066696C6520696E746F2061727261790A6946696C65203D206F70656E28696E70757466696C652C2027722729200A696E7075744172726179203D206946696C652E7265616428292E73706C69746C696E65732829200A6946696C652E636C6F736528290A0A202020200A0A6F46696C65203D206F70656E286F757470757466696C652C20277727292023777269746520746F2066696C650A666F72206C696E6520696E20696E70757441727261793A0A230A202020207472793A0A20202020202020202370726F6365737320746865206C696E657320686572650A20202020202020206966206C696E652E7374617274737769746828227B2229203A0A202020202020202020202020236A736F6E20737472696E6720696E2074686973206578616D706C65206861732027207265706C6163696E6720220A2020202020202020202020206C696E65203D206C696E652E7265706C616365282227222C225C2222290A2020202020202020202020207061727365645F6A736F6E203D206A736F6E2E6C6F616473286C696E65290A202020202020202020202020232365787472616374696E6720746865206B6579206D65737361676549640A20202020202020202020202070726F6365737365643D737472287061727365645F6A736F6E5B2773616C65275D290A2020202020202020656C73653A0A20202020202020202020202070726F6365737365643D22220A20202020202020200A20202020202020200A202020206578636570743A0A202020202020202070726F6365737365643D2222202361737369676E20746F206E756C6C206F6E20657863657074696F6E0A23656E646966202020200A230A202020206F46696C652E77726974652870726F6365737365642B275C6E2729200A6F46696C652E636C6F736528290A2020202020202020202020202020202020202020200A",
    "project": "ExtractJSON"
  }
]
```