postgres store reference to field in json

Question

It is possible to store json in postgres using the json data type. Check this tutorial for an introduction: http://www.postgresqltutorial.com/postgresql-json/

Consider I am storing the following json in such a field:

{
  "address": {
    "street1": "123 seasame st"
  }
}

I want a to store separately a reference to the street field. For example, I might have another object which is using data from this json structure and wants to store a reference to where it got the data. Maybe something like this:

class Product():
    __tablename__ = 'Address'

    street_1 = Column(String)
    data_source = ?

Now I could make data_source a string and just store namespaces like address.street, but if I did this postgres has no idea what that means. Working with that in queries would mean parsing the string and other inefficient stuff. Does postgres support referring to fields stored inside json data structures?

This question is related to JSON foreign keys in PostgreSQL , but in this case I don't necessarily want a fk relationship. I just want to create a reference, which is not necessarily enforced in the way a fk is.

update:

To be more clear, I want to reference the location of something in the json structure on another attribute and store that reference in a column. In the below code, Address.data_source is a reference to the location of the street data (for example address.street1 in this case)

class Address():
    __tablename__ = 'Address'

    street_1 = Column(String)
    sample_id = Column(Integer, ForeignKey('DataSample.uid'))

    data_source = ?

class DataSample():
    __tablename__ = 'DataSample'
    uid = Column(Integer, primary_key=True)
    data = Column(JSONB)


body = {
  "address": {
    "street1": "123 seasame st"
  }
}

datasample = DataSample(data=body)
address = Address(street_1=datasample.data['address']['street_1'], 
                  sample_id=datasample.uid,
                  data_source=?)

Is that SQLAlchemy? If so, this question should have been tagged with it, and I will not be addressing it. — MBer, Jun 03 '19 at 21:46
It's just a way of expressing the situation concsisely. I'm not interested in the sqlalchemy way of implementing a solution. I'm interested in: if you had a table `Address` how can you make a reference to a point in a JSONB data structure stored on a foreign object (`datasample.data[address][street1]`), in postgres. — melchoir55, Jun 03 '19 at 21:58

MBer · Accepted Answer · 2019-06-04T00:20:36.817

As clarified, the question is seeking a way to flexibly specify a path within a JSON object of a particular record. Keys are being handled in normal columns. Constraints on JSONB fields are not available, and there is no specific support for specifying paths within JSON objects.

I worked with the following in SQL Fiddle using PostgreSQL 9.6:

CREATE TABLE datasample (
  id integer PRIMARY KEY,
  data jsonb
);
CREATE TABLE address (
  id integer PRIMARY KEY,
  street_1 text,
  sample_id integer REFERENCES datasample (id),
  data_source text
);
INSERT INTO datasample(id, data) 
VALUES (1, '{"address":{"street_1": "123 seasame st"}}');
INSERT INTO address(id,street_1, sample_id, data_source) 
VALUES (1,'123 seasame st',1,'datasample.data->''address''->>''street''');

A typical lookup of the street address (needed to retrieve street_1) would resemble:

SELECT datasample.data->'address'->>'street_1' 
FROM datasample 
WHERE id=1;

There is no special postgres type for identifying columns. Strings are the closest available and you will need to retrieve the string (or array of strings, or object containing strings, if one of those simplifies parsing) and use it to build the query. In tbe first code block, I stored it as the (escaped) fragment of query - 'datasample.data->''address''->>''street'''. Though longer, it would require only retrieval and unescaping to use in a new custom query. I did not find a way to use the string as a fragment within the same SQL statement, though it might be possible to combine it with other bits of text to form a full statement that could be run through EXECUTE.

My attempt to simplify my example went too far. I meant to ask how to store a reference, rather than how to query json. I have updated my question with a more clear examole. — melchoir55, Jun 03 '19 at 21:23

postgres store reference to field in json

1 Answers1